Query to show result in column

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)

Query to show result in column

Author

Topic

imughal
Posting Yak Master

192 Posts

Posted - 2009-12-21 : 00:59:51

I m using this query which produced result but i want to have result in different format as given below pls help.

Select distinct Desc ,
(
Case when S.sfield = 'F1' then P.F1
when S.sfield = 'F2' then P.F2
when S.sfield = 'F3' then P.F3
when S.sfield = 'F4' then P.F4
when S.sfield = 'F5' then P.F5
when S.sfield = 'F6' then P.F6
when S.sfield = 'F7' then P.F7
when S.sfield = 'F8' then P.F8
when S.sfield = 'F9' then P.F9
when S.sfield = 'F10' then P.F10
when S.sfield = 'F11' then P.F11
when S.sfield = 'F12' then P.F12
when S.sfield = 'F13' then P.F13
when S.sfield = 'F14' then P.F14
when S.sfield = 'F15' then P.F15
when S.sfield = 'F16' then P.F16
when S.sfield = 'F17' then P.F17
when S.sfield = 'F18' then P.F18
when S.sfield = 'F19' then P.F19
when S.sfield = 'F20' then P.F20
when S.sfield = 'F21' then P.F21
when S.sfield = 'F22' then P.F22
when S.sfield = 'F23' then P.F23
when S.sfield = 'F24' then P.F24
when S.sfield = 'F25' then P.F25
when S.sfield = 'F26' then P.F26
when S.sfield = 'F27' then P.F27
when S.sfield = 'F28' then P.F28
when S.sfield = 'F29' then P.F29
when S.sfield = 'F30' then P.F30
when S.sfield = 'F31' then P.F31
when S.sfield = 'F32' then P.F32
when S.sfield = 'F33' then P.F33
when S.sfield = 'F34' then P.F34
when S.sfield = 'F35' then P.F35
when S.sfield = 'F36' then P.F36
when S.sfield = 'F37' then P.F37
when S.sfield = 'F38' then P.F38
when S.sfield = 'F39' then P.F39

else 'null'

end

) as val

from Table1 P
inner join Table1 S on
P.name = S.name and P.Device = S.Device and P.DeviceID =S.DID and P.sID = S.sID
where P.name in ('P1','P2','P3' ) and P.Device = @Device and DeviceID =@sID and sID= @sID

Result set:
P1 Fieldname filevalue1
P2 Fieldname filevalue2
P3 Fieldname filevalue3

P1 Fieldname filevalue11
P2 Fieldname filevalue22
P3 Fieldname filevalue33

P1 Fieldname filevalue13
P2 Fieldname filevalue23
P3 Fieldname filevalue33

Required Result

Field Name P1 P2 P3
abc Fieldvalue1 fieldvalue2 fieldvalue3

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2009-12-21 : 08:53:35

Instead of using 1 case statement and one column do 39 case statements with 39 columns.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources