CODEWITHSUNDEEP
sql-serverpivot
Soheil Bijavar
Soheil Bijavar

Reputation: 83

SQL Server pivot query returns only one row

My data is like this: 

ID  ColumnName  columnnvalue
----------------------------- 
13  seraialnew   tester name
13  serial        123123
13  seraialnew    am444
13  serial        33333


SELECT  id,seraialnew,serial
  FROM (
   SELECT id,columnnvalue, ColumnName  FROM dbo.formValues
  ) cols
PIVOT(
    MAX(id)
    FOR ColumnName IN(seraialnew,serial)
)p

and get single row result instead multiple

Upvotes: 2

Views: 2709

Answers (1)

gofr1
gofr1

Reputation: 15997

If you got same id there will be only one string, so I changed the id's for 2 of your rows:

;WITH formValues AS (
SELECT *
FROM (VALUES
(13,'seraialnew','tester name'),
(13,'serial','123123'),
(14,'seraialnew','am444'),
(14,'serial','33333')
) as t(ID, ColumnName, columnnvalue)
)

SELECT  id,seraialnew,serial
  FROM (
   SELECT id,columnnvalue, ColumnName
   FROM formValues
  ) cols
PIVOT(
    MAX(columnnvalue)
    FOR ColumnName IN(seraialnew,serial)
)p

Will give you:

id  seraialnew  serial
13  tester name 123123
14  am444       33333

Another way:

SELECT *
FROM (
    SELECT  id,
            columnnvalue,
            ColumnName+CAST(ROW_NUMBER() OVER (PARTITION BY ColumnName ORDER BY ColumnName) as nvarchar(1)) as ColumnName
    FROM formValues
) cols
PIVOT(
    MAX(columnnvalue)
    FOR ColumnName IN(seraialnew1,serial1,seraialnew2,serial2)
)p

With same id = 13 will give you this:

id  seraialnew1 serial1 seraialnew2 serial2
13  am444       33333   tester name 123123

Upvotes: 2

Related Questions