SQL Select Question - SQL Server Forums

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)

SQL Select Question

Author

Topic

Sathoris
Starting Member

5 Posts

Posted - 2009-11-30 : 09:52:19

Hi,

I'm trying to change the format of a query result from:

ID Name
1 NULL
1 Name1
1 Name2
2 Name2
3 Name3
4 NULL

(Have a total of 5 options as Name1-5)

Into

ID Name1 Name2 Name3 Name4 Name5
1 1 1 0 0 0
2 0 1 0 0 0
3 0 0 1 0 0
4 0 0 0 0 0

I'm using SQL 2000, I've saw this can be used simple with PIVOT which i don't have it in this SQL server version.

Thanks,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-11-30 : 09:57:45

[code]SELECT ID,
MAX(CASE WHEN Name='Name1' THEN 1 ELSE 0 END) AS Name1,
MAX(CASE WHEN Name='Name2' THEN 1 ELSE 0 END) AS Name2,
MAX(CASE WHEN Name='Name3' THEN 1 ELSE 0 END) AS Name3,
...
MAX(CASE WHEN Name='Name5' THEN 1 ELSE 0 END) AS Name5
FROM Table
GROUP BY ID
[/code]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-30 : 09:57:54

Refer this
http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

Madhivanan

Failing to plan is Planning to fail

Sathoris
Starting Member

5 Posts

Posted - 2009-11-30 : 10:04:07

Thanks visakh16

Your example works perfectly.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-11-30 : 10:05:18

welcome

Sathoris
Starting Member

5 Posts

Posted - 2009-11-30 : 10:28:12

@visakh16

Is there a way to update your query to work with unknown values ?

As in Name1, Name2, Name3 can be anything from a list of 50+ but i want to show only the first 5 (A-Z order).

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-11-30 : 10:33:08

quote:
Originally posted by Sathoris

@visakh16

Is there a way to update your query to work with unknown values ?

As in Name1, Name2, Name3 can be anything from a list of 50+ but i want to show only the first 5 (A-Z order).

then follow the method given by Madhi

Sathoris
Starting Member

5 Posts

Posted - 2009-11-30 : 11:45:18

Thanks, I've tried Madhi's example and it worked perfectly but the results were not what I expected and I think I've expressed myself wrong.

I'll try to give an example on how would be nice to have the results.

Original Values:
ID Name
1 NULL
1 Name1
1 Name2
1 Name5
2 Name4
2 Name10
3 Name3
4 NULL
4 Name12

Desired Results:
ID 1stOption 2ndOption 3rdOption
1 Name1 Name2 Name5
2 Name4 Name10
3 Name3
4 Name12

The idea is to create an additional column only when there is a duplicated row with the same ID that is not null.

I did this kind of reporting with C# code taken from the "Original Values" table, but in this case i need to do this with SQL only.

If there is a way to get the the first value from each ID's into one select, then the 2nd values for each Id if it exists into a second select as in:

This kind of results also works for me if it's easier to reproduce then the one above.

Results #1:
ID 1stOption
1 Name1
2 Name4
3 Name3
4 Name12

Result #2:
ID 2ndOption
1 Name2
2 Name10

Result #3:
ID 3rdOption
1 Name5

Sathoris
Starting Member

5 Posts

Posted - 2009-11-30 : 12:14:53

I've searched a bit this forum and i found an example to my 2nd request: http://sqlblogcasts.com/blogs/madhivanan/archive/2008/09/12/return-top-n-rows.aspx

Thanks problem solved.

In case someone might know a solution to the first result it would be nice to have.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-11-30 : 13:06:01

quote:
Originally posted by Sathoris

I've searched a bit this forum and i found an example to my 2nd request: http://sqlblogcasts.com/blogs/madhivanan/archive/2008/09/12/return-top-n-rows.aspx

Thanks problem solved.

In case someone might know a solution to the first result it would be nice to have.

do you have any other unique valued column in your table? may be an id or date field?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources