Need help with query - 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)

Need help with query

Author

Topic

john0990
Starting Member

4 Posts

Posted - 2009-02-02 : 12:47:21

Hello, I am working in SQL Server 2005 and trying to create a query on a table containing data similar to this:

SOrder FlName AlpValue
000024 ENT001 ......
000024 FAR001 DSmith
000024 HER001 ......
000025 ENT001 Yellow
000025 FAR001 JJones
000025 HER001 ......
000026 ENT001 ......
000026 FAR001 MCooke
000026 HER001 123456

into a resultset like this:

SOrder ENT001 FAR001 HER001
000024 ...... DSmith ......
000025 Yellow JJones ......
000026 ...... MCooke 123456

Any suggestions, help would be much appreciated!
Thanks in advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-02 : 12:52:13

[code]
SELECT SOrder,
MAX(CASE WHEN FlName='ENT001' THEN AlpValue ELSE NULL END) AS ENT001,
MAX(CASE WHEN FlName='FAR001' THEN AlpValue ELSE NULL END) AS FAR001,
MAX(CASE WHEN FlName='HER001' THEN AlpValue ELSE NULL END) AS HER001
FROM Table
GROUP BY SOrder
[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-02 : 12:54:29

or even this


SELECT SOrder,[ENT001],[FAR001],[HER001]
FROM
(SELECT SOrder, FlName, AlpValue FROM Table)m
PIVOT(MAX(AlpValue) FOR FlName IN ([ENT001],[FAR001],[HER001]))p

john0990
Starting Member

4 Posts

Posted - 2009-02-02 : 16:22:49

Thanks a million! I tried your first suggestion and it works like a champ!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-03 : 09:33:45

welcom...

try second also if you're on 2005

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources