CODEWITHSUNDEEP
sqlsql-serverpivot
AshJam
AshJam

Reputation: 35

Convert Data in a Column to a row in SQL Server

Fairly new to SQL, so I do apologise!

Currently I have the following SQL Query:

select [data]
from Database1.dbo.tbl_Data d
join Database1.tbl_outbound o on d.session_id = o.session_id
where o.campaign_id = 1047
  and d.session_id = 12

This returns ONE column which looks like this (and it can return different number of rows, depending on campaign_id and session_id!):

    [data]

[1] Entry 1
[2] Entry 2
[3] Entry 3
[4] Entry 4
[5] Entry 5
    .....
[98] Entry 98
[99] Entry 99

I would like to convert the data so they are displayed in 1 row and not 1 column, for example:

    [data1]  [data2]  [data3]  [data4]  [data5] .... [data98]  [data99]

[1] Entry 1  Entry 2  Entry 3  Entry 4  Entry 5 .... Entry 98  Entry 99

I hope I have explained that well enough! Thanks! :)

I have seen some information floating around about Pivot and Unpivot, but couldn't get it to play ball!

Upvotes: 0

Views: 106

Answers (3)

Sreenu131
Sreenu131

Reputation: 2516

Try This Dynamic sql which helps your requirement

IF OBJECT_ID('tempdb..#Temp')IS NOT NULL
DROP TABLE #Temp
CREATE TABLE #Temp (data VARCHAR(100))
GO
IF OBJECT_ID('tempdb..#FormatedTable')IS NOT NULL
DROP TABLE #FormatedTable
Go

INSERT INTO #Temp(data)
SELECT  'Entry1' UNION ALL
SELECT  'Entry2' UNION ALL
SELECT  'Entry3' UNION ALL
SELECT  'Entry4' UNION ALL
SELECT  'Entry5'

SELECT ROW_NUMBER()OVER(ORDER BY Data) AS SeqId, 
        Data,
        'Data'+CAST(ROW_NUMBER()OVER(ORDER BY Data) AS VARCHAR(100)) AS ReqColumn
INTO #FormatedTable
FROM #Temp

DECLARE  @Sql nvarchar(max),
         @DynamicColumn nvarchar(max),
         @MaxDynamicColumn nvarchar(max)


SELECT @DynamicColumn = STUFF((SELECT  ', '+QUOTENAME(ReqColumn)
FROM #FormatedTable  FOR XML PATH ('')),1,1,'') 

SELECT @MaxDynamicColumn = STUFF((SELECT  ', '+'MAX('+(ReqColumn)+') AS '+QUOTENAME(CAST(ReqColumn AS VARCHAR(100)))
FROM #FormatedTable   FOR XML PATH ('')),1,1,'') 

SET @Sql=' SELECT ROW_NUMBER()OVER(ORDER BY (SELECT 1)) AS SeqId, '+ @MaxDynamicColumn+'
            FROM
            (
            SELECT * FROM #FormatedTable
            ) AS src
            PIVOT 
            (
            MAX(Data) FOR [ReqColumn] IN ('+@DynamicColumn+')
            ) AS Pvt
            '
EXEC (@Sql)
PRINT @Sql

Result

SeqId   Data1   Data2   Data3   Data4   Data5
----------------------------------------------
1       Entry1  Entry2  Entry3  Entry4  Entry5

Upvotes: 1

user11380812
user11380812

Reputation:

The easiest way to do that is to utilize SQLCLR.

Check out the solution and explanation on An Easier Way of Transposing Query Result in SQL Server

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269583

There is no really simple way. You can use pivot or conditional aggregation. I prefer the latter:

select max(case when left(data, 3) = '[1]' then data end) as data_001,
       max(case when left(data, 3) = '[2]' then data end) as data_002,
       max(case when left(data, 5) = '[100]' then data end) as data_100       
from Database1.dbo.tbl_Data d join
     Database1.tbl_outbound o
     on d.session_id = o.session_id
where o.campaign_id = 1047 and d.session_id = 12;

Note that the columns are fixed, so you will always have 100 columns, regardless of the number of actual values in the data.

If you need a flexible number of columns, then you need dynamic pivoting, which requires constructing the query as a string and then executing the string.

Upvotes: 0

Related Questions