MS SQL

MS SQL RSS

sql convert vertical to horizontal

Please support our MS SQL advertiser: Intel Parallel Studio Home

Thread Solved

•

Join Date: Oct 2009

Posts: 4

Reputation: azamsalam is an unknown quantity at this point

Solved Threads: 0

azamsalam

Offline

Newbie Poster

sql convert vertical to horizontal

28 Days Ago

I am using SQL Server 2005. I have a data from SQL like this.

ID ID2 PartNo

75 23921 DENT
75 26145 PLGD
75 26145 PRRP
75 26145 SIST
75 57290 PRMD
75 57290 abc
75 57290 def
75 57290 ghi
75 57290 jkl

I need an output like this. (max Part4)

ID ID2 Part1 Part2 Part3 Part4

75 23921 DENT
75 26145 PLGD PRRP SIST
75 57290 PRMD abc def ghi

Thanks

•

Join Date: Feb 2009

Posts: 3,187

Reputation: sknake has much to be proud of

Solved Threads: 571

sknake

Offline

.NET Enthusiast

28 Days Ago

This is commonly referred to as a pivot.

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
IF OBJECT_ID('tempdb..#Parts', 'U') IS NOT NULL DROP TABLE #Parts
CREATE TABLE #Parts
(
  ID int,
  ID2 int,
  PartNo varchar(10)
)
SET NOCOUNT ON
INSERT INTO #Parts (ID, ID2, PartNo) Values (75, 23921, 'DENT')
INSERT INTO #Parts (ID, ID2, PartNo) Values (75, 26145, 'PLGD')
INSERT INTO #Parts (ID, ID2, PartNo) Values (75, 26145, 'PRRP')
INSERT INTO #Parts (ID, ID2, PartNo) Values (75, 26145, 'SIST')
INSERT INTO #Parts (ID, ID2, PartNo) Values (75, 57290, 'PRMD')
INSERT INTO #Parts (ID, ID2, PartNo) Values (75, 57290, 'abc')
INSERT INTO #Parts (ID, ID2, PartNo) Values (75, 57290, 'def')
INSERT INTO #Parts (ID, ID2, PartNo) Values (75, 57290, 'ghi')
INSERT INTO #Parts (ID, ID2, PartNo) Values (75, 57290, 'jkl')
SET NOCOUNT OFF
 
SELECT ID, ID2,
Max((Case When RowNumber = 1 Then PartNo Else '' End)) AS Part1,
Max((Case When RowNumber = 2 Then PartNo Else '' End)) AS Part2,
Max((Case When RowNumber = 3 Then PartNo Else '' End)) AS Part3,
Max((Case When RowNumber = 4 Then PartNo Else '' End)) AS Part4
FROM
(
  SELECT ID, ID2, PartNo,
  ROW_NUMBER() OVER(PARTITION BY ID, ID2 ORDER BY PartNo ASC) AS RowNumber
  FROM #Parts
) AS tbl
GROUP BY ID, ID2
IF OBJECT_ID('tempdb..#Parts', 'U') IS NOT NULL DROP TABLE #Parts
Create Table #Parts
(
  ID int,
  ID2 int,
  PartNo varchar(10)
)
SET NOCOUNT ON
Insert Into #Parts (ID, ID2, PartNo) Values (75, 23921, 'DENT')
Insert Into #Parts (ID, ID2, PartNo) Values (75, 26145, 'PLGD')
Insert Into #Parts (ID, ID2, PartNo) Values (75, 26145, 'PRRP')
Insert Into #Parts (ID, ID2, PartNo) Values (75, 26145, 'SIST')
Insert Into #Parts (ID, ID2, PartNo) Values (75, 57290, 'PRMD')
Insert Into #Parts (ID, ID2, PartNo) Values (75, 57290, 'abc')
Insert Into #Parts (ID, ID2, PartNo) Values (75, 57290, 'def')
Insert Into #Parts (ID, ID2, PartNo) Values (75, 57290, 'ghi')
Insert Into #Parts (ID, ID2, PartNo) Values (75, 57290, 'jkl')
SET NOCOUNT OFF

Select ID, ID2,
Max((Case When RowNumber = 1 Then PartNo Else '' End)) As Part1,
Max((Case When RowNumber = 2 Then PartNo Else '' End)) As Part2,
Max((Case When RowNumber = 3 Then PartNo Else '' End)) As Part3,
Max((Case When RowNumber = 4 Then PartNo Else '' End)) As Part4
From
(
  Select ID, ID2, PartNo,
  ROW_NUMBER() OVER(PARTITION BY ID, ID2 ORDER BY PartNo ASC) AS RowNumber
  From #Parts
) As tbl
Group By ID, ID2

Results in:

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
ID          ID2         Part1      Part2      Part3      Part4
----------- ----------- ---------- ---------- ---------- ----------
75          23921       DENT                             
75          26145       PLGD       PRRP       SIST       
75          57290       abc        def        ghi        jkl
 
(3 row(s) affected)
ID          ID2         Part1      Part2      Part3      Part4
----------- ----------- ---------- ---------- ---------- ----------
75          23921       DENT                             
75          26145       PLGD       PRRP       SIST       
75          57290       abc        def        ghi        jkl

(3 row(s) affected)