Rows to one row with dynamic columns

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

Rows to one row with dynamic columns

Author

Topic

conedm
Starting Member

33 Posts

Posted - 2010-03-24 : 12:14:07

Here is my problem...
I have a table with locations (SITES)
SITE
1
2
3
4 etc
I have a seperate table with resources that get assigned to those sites

ID SITE TYPE
1 2 'test'
2 2 'test1'
3 2 'test3'
etc
What I need is a result set that shows dynamic columns like this:
SITE TYPE1 TYPE2 TYPE3
2 'test' 'test1' 'test3'
I a .net programmer. I assume I would need a pivot table for this?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-24 : 12:16:18

yeah. but before that will you be certain on number of type values that exist for site?

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

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-24 : 12:16:19

Since SQL Server 2005 you can use PIVOT:
http://msdn.microsoft.com/en-us/library/ms177410(SQL.90).aspx

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-24 : 12:17:28

quote:
Originally posted by visakh16

yeah. but before that will you be certain on number of type values that exist for site?

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

Yes you're right the number of different type values is needed.

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-24 : 12:18:17

if sql 2005 or later
http://beyondrelational.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

if earlier version

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

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

conedm
Starting Member

33 Posts

Posted - 2010-03-24 : 12:32:08

It is SQL Server 2005
I will check into those links and reply what I come up with....Thank you for the help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-24 : 12:34:41

good luck..come back if you face any issues

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

conedm
Starting Member

33 Posts

Posted - 2010-03-24 : 16:55:19

When I try to create a stored procedure for this it tries to save it locally on my computer. How do I get it to save so that it shows up in the object explorer???

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-25 : 02:57:22

quote:
Originally posted by conedm

When I try to create a stored procedure for this it tries to save it locally on my computer. How do I get it to save so that it shows up in the object explorer???

Run the code at Server
After resresh database and see

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-25 : 11:41:36

or just try

sp_helptext 'procedurename'

it will show the created proc body

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

conedm
Starting Member

33 Posts

Posted - 2010-03-29 : 14:30:20

Ok thanks, I was trying to save it rather that run it first...
I worked for a while on the code but the pivot table I was looking at is for an aggregate function.
I don't need to run any aggregate function with it, I just need to display the field data.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-30 : 13:19:42

pivoting or cross tabbing by itself is an aggregate operation so you need to apply aggregate function like MIN() or MAX() even if you just need to display the data

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources