SQL query needed - 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 query needed

Author

Topic

srivatsahg
Yak Posting Veteran

71 Posts

Posted - 2009-07-01 : 07:48:37

Hello Programmers..
I need a sql query similar to PIVOT transformation.
I have a table containing 3 Columns ...


STEP | Label | Value 
--------------------
1    | Good  | Enabled
2    | Better| Enabled
3    | Best  | Disabled

I need a sql query to get the results as


STEP | Good     | Better  | Best
--------------------------------------------
1    | Enabled  | NULL    | NULL
2    | NULL     | Enabled | NULL
3    | NULL     | NULL    | Disabled

Looks the query needs to display the transpose of a matrix
Any help in this direction appreciated

Srivatsa

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-01 : 07:54:36

According to Books Online,

SELECT p.Step, p.Good, p.Better, p.Best
FROM Table1 AS t
PIVOT (
MAX(Value) FOR Label IN ([Good], [Better], [Best])
) AS p

N 56°04'39.26"
E 12°55'05.63"

srivatsahg
Yak Posting Veteran

71 Posts

Posted - 2009-07-01 : 08:13:12

Hello Peso

Thanks a ton for your help.
The query works like a butter.
I was aware using PIVOT operator but wasnt aware using the MAX operator to return non-integer fields as results

Thanks again
Srivatsa

quote:
Originally posted by Peso

According to Books Online,

SELECT p.Step, p.Good, p.Better, p.Best
FROM Table1 AS t
PIVOT (
MAX(Value) FOR Label IN ([Good], [Better], [Best])
) AS p

N 56°04'39.26"
E 12°55'05.63"

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-07-01 : 08:15:16

Hi try this too

drop table #temp

create table #temp ( STEP varchar(32),Label varchar(32),Value varchar(32) )
insert into #temp select 1 ,'Good' ,'Enabled'
insert into #temp select 2 ,'Better','Enabled'
insert into #temp select 3 ,'Best' ,'Disabled'

DECLARE @strcols VARCHAR(MAX), @values VARCHAR(MAX),@string VARCHAR(MAX)

select @strcols = '' , @values = '' ,@string = ''

SELECT @strcols = @strcols + ', ([' + Label + ']) ',
@values = @values + ', ['+ Label + ']'
from ( select distinct Label from #temp ) t

select @string = 'select STEP'+@strcols+'
from #temp
pivot(max(Value) for Label in (' + stuff(@values,1,1,'') + '))p'
print(@string)
exec(@string)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-01 : 12:38:21

whats the need of dynamic sql here. the values to pivot are static so peso's suggestion or below will be enough


SELECT STEP,
MAX(CASE WHEN Label='Good' THEN Value ELSE NULL END) AS Good,
MAX(CASE WHEN Label='Better' THEN Value ELSE NULL END) AS Better,
MAX(CASE WHEN Label='Best' THEN Value ELSE NULL END) AS Best
FROM Table
GROUP BY STEP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources