Column + Value in query result 1

[Andel]

Hi, is there a way I can combine the column name and its value in the result set? For example:

Sample data:

AccID Name Age
----- ------ ----
111 Andy 25


Desired Result:
-------------------------
AccID = 111
Name = Andy
Age = 25

Any idea?

Andel
andelbarroga@hotmail.com

http://andyb.8m.com

 

[tlbroadbent]

The following solution is not elegant but should work if C1 is the primary key or clustered index. Of course, you can add or delete fields in the UNION query. You will also define your own selection criteria.

Select a.Col From
(
select C1, Col="C2 = " + C2 From dbo.MyTbl Where C5=1 And C6='Y'
Union
select C1, "C3 = " + C3 From dbo.MyTbl Where C5=1 And C6='Y'
Union
select C1, "C4 = " + C4 From dbo.MyTbl Where C5=1 And C6='Y'
) AS a

Let me know if you have any questions.

 

[Andel]

Hi tlbroadbent,

Thanks for responding.
I'm sorry but I'm confuse about your query.
Can you please explain. Thanks.


Andel
andelbarroga@hotmail.com

http://andyb.8m.com

 

[tlbroadbent]

Let me see if I can explain the query. I'll use the column names from your example.

Select a.DesiredResult From
(
Select AccID, DesiredResult="AccID = " + str(AccID) From MyTbl
Union
Select AccID, "Name = " + Name From MyTbl
Union
Select AccID, "Age = " + str(Age) From MyTbl
) AS a

1- Inside the parentheses I have created a union query. Each Select statement in the union query selects the unique key column and one of the data columns you want to reformat. The key column is important because it will keep all the rows with the same unique key together. In this example, I assume AccID is a unique key.

2- The second column of the inner query is named "DesiredResult" on the first Select statement of the union query. Note that the following Select statements in the union query do not require a column name.

3- The "AS a" clause at the end of the union query aliases the inner query as "a" for reference by the outer query. This clause must exist and be outside of the trailing parenthesis.

4- "Select a.DesiredResult From" is the outer query that selects the second column from the union or inner query.

5- SQL will not concatenate strings with numbers so I assumed that AccID and Age were numeric and used the str function to convert those columns to strings in order to concatenate to the column name.

6- In my testing the results were ordered in sequence of the key column and the second column. In this case, the three rows of data per set will be in AccID, Age, Name order not AccID, Name, Age as in the query.

I hope this helps. Please feel free to ask additional questions.

 

[Andel]

Yehey!!! it works very well!
I just needed to change the " to '.
Thanks.

Andel
andelbarroga@hotmail.com

http://andyb.8m.com