Need to display column data as names dynamically

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

General SQL Server Forums

New to SQL Server Programming

Need to display column data as names dynamically

Author

Topic

soori457
Yak Posting Veteran

85 Posts

Posted - 2010-07-30 : 15:08:24

I have two tables with names "PRODUCT" and "GROUPPROFILE" with the following data.

PRODUCT
PRODUCTCODE PRODUCTNAME
1 ECS
2 TMS
3 TAS

GROUPPROFILE
GROUPCODE PRODUCTCODE DEFAULTFLAG
1 1 Y
1 2 Y
2 1 Y
2 3 Y
2 2 Y
3 2 Y
3 3 Y
4 1 Y
4 3 Y
5 2 Y
5 3 Y

I need to retrieve data like
PRODUCT NAMES as column names and
GROUP CODES as column values. i.e., the final result should be like in the following way.

ECS TMS TAS (PRODUCT NAME as Column Names)
1 1 NULL (GROUP CODE as Column Values)
2 2 2
NULL 3 3
4 NULL 4
NULL 5 5

EXPLANATION:
One group Code can have many products. This relation is made in GROUPPROFILE Table.
These products are stored in product table.

Now I need to display data in such a way that PRODUCT is related to the GROUPCODE

Example result is showed above.

Any help would be greatly appreciated.
Thanks in advance.
Suresh

Suresh Kumar

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-07-31 : 02:41:30

Try this:

Select [ECS],[TMS],[TAS]
From
(
SELECT GROUPCode, [ECS],[TMS],[TAS]
FROM
(
SELECT GROUPCode G1, G.GROUPCode,P.PRODUCTNAME FROM
GROUPPROFILE G INNER JOIN PRODUCT P
ON G.PRODUCTCODE = P.PRODUCTCODE ) AS SourceTable
PIVOT
(
max(G1)
for PRODUCTNAME in ([ECS],[TMS],[TAS])
) as pivottable
) as SubTab

If you have many products than you can use the dynamic pivot.
http://beyondrelational.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

soori457
Yak Posting Veteran

85 Posts

Posted - 2010-07-31 : 13:20:41

Hi Bohra,

your query helped me a lot.
Thanks a lot!!!

Suresh Kumar

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-08-01 : 02:34:10

You are welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources