columnname in sql

how to select all column names from sql whose columnvalue is '1' for a specific user

Could you please post the table structure? Ideally the Create Table SQL.

Thanks,

Matt

|||

Thankyou for the response Matt.

Actually the entire column in the table is dynamically created.it will grow everytime wen user add .but it wont happen frequently.so i cant post that table struct.let me describe my needs with this sample

Create table sample(id varchar(10), item1 bit,item2 bit,item3 bit,item4 bit)...... and goes on

now i need to select all column names ,actually i can do this.

but i jus need to select columns whose value is one.

eg.

id item1 item2 item3 item4

zzz 1 0 0 1

yyy 0 1 1 1

my sql qurey or SP should return the column name for specific id whose value is one.

lets say id=zzz it should returnItem1 and item4(i meant column name)

if id = yyy it should return item2,item3,item4(i meant column name)

Thanks in advance

|||

HiTweety@.net,

As far as i know you need to use cursor in your case. But where are planing to store those column names??

See the following codes i've written for you:

declare @.item1int,@.item2int,@.item3int,@.item4intdeclare cursor_test cursorforselect * from test_tblopen cursor_testfetch next from cursor_testwhile @.@.fetch_status=0beginfetch next from cursor_testinto @.item1,@.item2,@.item3,@.item4if(@.item1=1)print'column 1 name'if(@.item2=1)print'column 2 name'if(@.item3=1)print'column 3 name'if(@.item4=1)print'column 4 name'endclose cursor_testdeallocate cursor_test

BTW, if possible, i would suggest you write the code in your application(using c# or other .net languages instead of T-SQL)

Hope my suggestion helps

|||

Hi Thanks for the Response,

i managed to solve this issue by retreiving the column names in table first and then checking the value for one using vb program

Column aliases in MDX

Is it possible to use column aliases in MDX query?
I execute MDX using MSOLAP and I'd like to have a specific names for the columns returned.
Linkedserver and OPENROWSET is a one solution, but is there a way to specify column aliases in MDX query?

You can use calculated members to change names of dimension members (including measures).

So you could write your query as something like the following:

WITH MEMBER Measures.[NiceName1] as Measures.M1, Measures.[NiceName2] as M2

SELECT {Measures.[NiceName1], Measures.[NiceName2]} on 0

FROM [MyCube]

|||Using CM for that purposes is a very dangerous way.|||Thank you.
When running some examples, I'm still getting errors:
1. "Parser: The syntax for '.' is incorrect." for:
WITH MEMBER Measures.[Reseller Sales Amount] as Measures.M1, Measures.[Calendar Year].[CY 2004] as M2
SELECT
([Measures].[Reseller Sales Amount],[Date].[Calendar Year].[CY 2004]) ON 0
FROM [Adventure Works]

2. "The Reseller Sales Amount calculated member cannot be created because a member with the same name already exists." for:
WITH MEMBER Measures.[Reseller Sales Amount] as M2
SELECT
([Measures].[Reseller Sales Amount],[Date].[Calendar Year].[CY 2004]) ON 0
FROM [Adventure Works]

You're saying: "Using CM for that purposes is a very dangerous way."
What would be the best? Linkedserver and OPENROWSET only?

|||

Try reversing the syntax. (In other words NiceName1 was the new calculated member serving as an alias to M1 in my original example.)

You will lose the ability to do drillthrough on the calc members. Vladamir can comment on other concerns he may have about this approache.

|||

Vladimir -

Why is this considered a bad practice? What other ways are there to accomplish this? You have me very curious...

Thanks,

John

Column aliases in MDX

Is it possible to use column aliases in MDX query?
I execute MDX using MSOLAP and I'd like to have a specific names for the columns returned.
Linkedserver and OPENROWSET is a one solution, but is there a way to specify column aliases in MDX query?

You can use calculated members to change names of dimension members (including measures).

So you could write your query as something like the following:

WITH MEMBER Measures.[NiceName1] as Measures.M1, Measures.[NiceName2] as M2

SELECT {Measures.[NiceName1], Measures.[NiceName2]} on 0

FROM [MyCube]

|||Using CM for that purposes is a very dangerous way.|||Thank you.
When running some examples, I'm still getting errors:
1. "Parser: The syntax for '.' is incorrect." for:
WITH MEMBER Measures.[Reseller Sales Amount] as Measures.M1, Measures.[Calendar Year].[CY 2004] as M2
SELECT
([Measures].[Reseller Sales Amount],[Date].[Calendar Year].[CY 2004]) ON 0
FROM [Adventure Works]

2. "The Reseller Sales Amount calculated member cannot be created because a member with the same name already exists." for:
WITH MEMBER Measures.[Reseller Sales Amount] as M2
SELECT
([Measures].[Reseller Sales Amount],[Date].[Calendar Year].[CY 2004]) ON 0
FROM [Adventure Works]

You're saying: "Using CM for that purposes is a very dangerous way."
What would be the best? Linkedserver and OPENROWSET only?

|||

Try reversing the syntax. (In other words NiceName1 was the new calculated member serving as an alias to M1 in my original example.)

You will lose the ability to do drillthrough on the calc members. Vladamir can comment on other concerns he may have about this approache.

|||

Vladimir -

Why is this considered a bad practice? What other ways are there to accomplish this? You have me very curious...

Thanks,

John