How to show the column value as column name

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to show the column value as column name

Author

Topic

ganny
Yak Posting Veteran

51 Posts

Posted - 2012-01-09 : 01:34:14

Dear All,

I would like to show the column value as column name. Please check the below raw table:

-------------------------------------------------------
Place....| Route1 | Route1_Date | Route2 | Route2_Date
-------------------------------------------------------
Chennai..| CH-01 | 01/01/2012 | BL-04 | 07/01/2012
Bangalore| CH-01 | 02/01/2012 | BL-04 | 08/01/2012
Pune.....| CH-01 | 03/01/2012 | BL-04 | 09/01/2012
Hyderabad| CH-01 | 04/01/2012 | BL-04 | 10/01/2012
-------------------------------------------------------

Now, I want the above table in the below format:

---------------------------------------
Place....| CH-01 | BL-04 |
---------------------------------------
Chennai..| 01/01/2012 | 07/01/2012 |
Bangalore| 02/01/2012 | 08/01/2012 |
Pune.....| 03/01/2012 | 09/01/2012 |
Hyderabad| 04/01/2012 | 10/01/2012 |
---------------------------------------

Please guide me how to show the above result.

Thank you in advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-09 : 01:40:37

will the routes be static? also will it be always maximum of 2 routes per place?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ganny
Yak Posting Veteran

51 Posts

Posted - 2012-01-09 : 02:34:19

Yes, routes are static and there are only two routes as mentioned.

Please assist solution.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-09 : 03:06:35

then below should work


SELECT  Place,
MAX(CASE WHEN Val='CH-01' THEN Route1_Date END) AS CH-01,
MAX(CASE WHEN Val='BL-04' THEN Route1_Date END) AS BL-04
FROM table
UNPIVOT (Val FOR Route IN (Route1,Route2))u
GROUP BY Place

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ganny
Yak Posting Veteran

51 Posts

Posted - 2012-01-09 : 03:51:03

Dear Sir,

sorry, i meant "Places" are static and route numbers & dates will update in the table accordingly. There are two routes (route1 & route2) but their values not known (not fixed values). It may also contain values as "PN-02, HY-03" with different dates.

Please advise me whether is it possible to put the column name with value of the column as i mentioned in the example table.

Thanks in advance.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-01-09 : 03:57:02

Yes. You will need to use Dynamic SQL. Read this http://www.sommarskog.se/dynamic_sql.html

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-09 : 04:19:27

quote:
Originally posted by ganny

Dear Sir,

sorry, i meant "Places" are static and route numbers & dates will update in the table accordingly. There are two routes (route1 & route2) but their values not known (not fixed values). It may also contain values as "PN-02, HY-03" with different dates.

Please advise me whether is it possible to put the column name with value of the column as i mentioned in the example table.

Thanks in advance.

for that see

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ganny
Yak Posting Veteran

51 Posts

Posted - 2012-01-09 : 04:38:55

No, i could not get the column value as column name. Please someone help me with solution.

Thanks.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-01-09 : 05:02:11