• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 365
  • Last Modified:

PIVOT SQL

Hi,
I would like to know the best way to pivot a table using sql.
The table that needs pivoting is below:

ID    TYPE      VALUE
1A      PLAN      Yes
1A      FINAL      No
1B      PLAN      Yes
1B      FINAL      No
1C      PLAN      Yes
1C      FINAL      No

The new table required is:

ID    PLAN   FINAL
1A      Yes          No
1B      Yes          No
1C      Yes          No

Thank you
CREATE TABLE [RESULT](
    [ID] [varchar](30) NOT NULL,
    [TYPE] [varchar](30) NOT NULL,
    [VALUE] [varchar](30) NOT NULL
)
GO

INSERT INTO RESULT VALUES('1A','PLAN','Yes')
INSERT INTO RESULT VALUES('1A','FINAL','No')
INSERT INTO RESULT VALUES('1B','PLAN','Yes')
INSERT INTO RESULT VALUES('1B','FINAL','No')
INSERT INTO RESULT VALUES('1C','PLAN','Yes')
INSERT INTO RESULT VALUES('1C','FINAL','No')
GO

select
ID
,min(CASE when [TYPE] = 'PLAN' then VALUE ELSE 'Null' END) as [PLAN] 
,min(CASE when [TYPE] = 'FINAL' then VALUE ELSE 'Null' END) as [FINAL] 
from RESULT 
group by ID
GO

DROP TABLE RESULT
GO

Open in new window

0
crompnk
Asked:
crompnk
  • 2
2 Solutions
 
Imran Javed ZiaConsultant Software Engineer - .NET ArchitectCommented:
Hi,
the one you used is one old approch now you can use Pivot clause in sql server 2005 and higher

SELECT *
FROM
(select ColumnName,ColumnValue,ColID from tbl
) as tab
PIVOT
 (
 min(ColumnValue)
 FOR ColumnName
 IN (ColID,ColumnName,ColumnValue)
 )
 AS p

you may find following helpful:

http://msdn.microsoft.com/en-us/library/ms177410.aspx
http://www.kodyaz.com/articles/t-sql-pivot-tables-in-sql-server-tutorial-with-examples.aspx
http://stackoverflow.com/questions/24470/sql-server-pivot-examples

Thanks
0
 
mayank_joshiCommented:
this should work:-

SELECT ID,
[PLAN],[FINAL]
FROM
(SELECT [TYPE],[VALUE],ID
    FROM YourTable) AS SourceTable
PIVOT
(
MIN(VALUE)
FOR [TYPE] IN ([PLAN],[FINAL])
) AS PivotTable;

Open in new window

0
 
mayank_joshiCommented:
FORGOT TO MENTION REPLACE YourTable WITH WHATEVER TABLE YOU ARE USING.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now