CODEWITHSUNDEEP
sqlsql-serverpivot-table
Ram
Ram

Reputation: 825

Displaying values from rows into columns in SQL Server

I have table contains below sample records.

select value, FieldID 
from [values] 
where fieldid in (140996, 140997)

Output:

value Fieldid
--------------
101    140996
102    140996
       140996
Louis  140997
Khan   140997
Akabar 140997

I want to display above values from rows into columns like below fieldid is input parameter and Value columns contains any no of rows.

Sample output

Fieldid    value1  value2  value3
---------------------------------
140996      101     102       
140997      Louis   Khan   Akabar

Upvotes: 1

Views: 3379

Answers (2)

D-Shih
D-Shih

Reputation: 46239

You can try to use condition aggregate function make pivot.

;WITH CTE AS(
   SELECT  *,ROW_NUMBER() OVER(PARTITION BY FieldId ORDER BY FieldId) rn
   FROM  [values]
)

SELECT FieldId,
    MAX(CASE WHEN rn = 1 THEN value END),
    MAX(CASE WHEN rn = 2 THEN value END),
    MAX(CASE WHEN rn = 3 THEN value END)
FROM CTE
GROUP BY FieldId

sqlfiddle

EDIT

If you want to let your columns created dynamically.

You can try to use dynamic pivot.

using CTE recursion make row number from min row to max row.

then use the row number write condition aggregate function SQL.

DECLARE @cols AS NVARCHAR(MAX) = '',
        @query AS NVARCHAR(MAX);


;WITH CTE AS(
   SELECT  *,ROW_NUMBER() OVER(PARTITION BY FieldId ORDER BY FieldId) rn
   FROM  [values]
), CTE2 AS (
   SELECT MIN(rn) minID,MAX(rn) maxID
   FROM CTE
   UNION ALL
   SELECT minID + 1,maxID
   FROM CTE2
   WHERE  minID + 1<=maxID
)

SELECT @cols = @cols + 'MAX(CASE WHEN rn ='+ cast(minID as varchar(5)) +' THEN value END) Value'+CAST(minID AS VARCHAR(5))+','
FROM CTE2

set @cols = substring(@cols,0,len(@cols))


set @query =  '
;WITH CTE AS(
   SELECT *,ROW_NUMBER() OVER(PARTITION BY FieldId ORDER BY FieldId) rn
   FROM  [values]
)
 SELECT FieldId,' + @cols + ' 
 FROM CTE
 GROUP BY FieldId'


execute(@query)

sqlfiddle

Upvotes: 1

LukStorms
LukStorms

Reputation: 29667

You can PIVOT on numbered columns generated via a CONCAT and ROW_NUMBER.

SELECT *
FROM
(
    SELECT FieldID, value, 
     CONCAT('value', ROW_NUMBER() OVER (PARTITION BY FieldId ORDER BY (select 0))) AS col
    FROM [values]
    -- WHERE fieldid IN (140996,140997)
) src
PIVOT
(
    MAX(Value)
    FOR col IN ([value1], [value2], [value3])
) pvt

You can test it here

Note that an order by nothing doesn't garantee the same order of the values. So if the table has a primary key then you could order by that instead.

Upvotes: 0

Related Questions