SQL How to display result as per below format

Question

I have a table in SQL Server 2014 with 2 columns as below

ObjectName   Whitelist
 A           Field1,Field2,Field3
 B           Field1,Field2

Using the below query I managed to print result as below

SELECT  ObjectName,
LTRIM(RTRIM(m.n.value('.[1]','varchar(8000)'))) AS WhiteList
FROM
(
SELECT ObjectName,CAST('<XMLRoot><RowData>' + REPLACE(WhiteList,',','</RowData><RowData>') + '</RowData></XMLRoot>' AS XML) AS x
FROM  dbo.testtable
)t
CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)

Actual Result using above query

ObjectName WhiteList
A          Field1
A          Field2
A          Field3
B          Field1
B          Field2

Expected Result

A           B   
Field1     Field1
Field2     Field2
Field3

@mohdmazharkhan Can you please share an example/link for reference. — Vikas J, Dec 11 '19 at 06:01
@VikasJ, [https://stackoverflow.com/a/15931734/3876565], check it... — DineshDB, Dec 11 '19 at 06:41

score 2 · Answer 1 · answered Dec 11 '19 at 06:15

You need to use Pivot to convert Rows to Colums.

Pivot is easier to implement on fixed columns.

For Dynamic Columns, based on result, you can write Dynamic Queries to acheive the result.

It is well explained in the Stackoverflow answer: Efficiently convert rows to columns in sql server

score 1 · Answer 2 · answered Dec 11 '19 at 08:00

CREATE TABLE #TEMP(ObjectName VARCHAR(5),   Whitelist VARCHAR(50))


INSERT INTO #TEMP(ObjectName, Whitelist) VALUES ( 'A','Field1,Field2,Field3')
INSERT INTO #TEMP(ObjectName, Whitelist) VALUES ( 'B','Field1,Field2')



SELECT A,B FROM
(   
    SELECT t1.ObjectName,t1.WhiteList,
    Row_Number() over(partition by t1.ObjectName order by t1.Whitelist) RN FROM ---Here We have to use Rownumber()...because they containing the same value
    (
        SELECT  ObjectName,
        LTRIM(RTRIM(m.n.value('.[1]','varchar(8000)'))) AS WhiteList
        FROM
        (
        SELECT ObjectName,CAST('<XMLRoot><RowData>' + REPLACE(WhiteList,',','</RowData><RowData>') + '</RowData></XMLRoot>' AS XML) AS x
        FROM  #TEMP
        )t
        CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)
    )t1   ---Here we will get value in comma seprated......then we can use Pivot
) AS TempTbl 
PIVOT
(
    MAX(WhiteList) --here you have to use max() function beacuse...your column value doesn't containg any int column...
    FOR ObjectName IN (A,B)
) AS PivotTable

Output:-

   A          B
  ------------------
    Field1  Field1
    Field2  Field2
    Field3  NULL

Number of ObjectName is not static (A and B) its count is dynamic. So there won't be always just 2 values in the ObjectName column. — Vikas J, Dec 11 '19 at 08:41
@@VikasJ What are you trying to Say that...instead column A and B....it should be dynamic ....right ?.. — THE LIFE-TIME LEARNER, Dec 11 '19 at 09:09

score 0 · Accepted Answer · answered Dec 12 '19 at 05:21

Thanks, everyone for your help. Through your comments and other helpful links on StackOverflow, I am able to capture the expected result which I had posted in my question (1st post). Sharing the solution so it might be useful to someone with a similar requirement.

If OBJECT_ID('tempdb..##tempList') is not null
drop table ##tempList

SELECT ObjectName,
LTRIM(RTRIM(m.n.value('.[1]','varchar(max)'))) AS ColumnName
into ##tempList
FROM
(
SELECT
ObjectName,
CAST('<XMLRoot><RowData>' + REPLACE(whitelist,',','</RowData><RowData>') + '</RowData></XMLRoot>' AS XML) AS x
FROM [dbo].[testtable]
where 1=1
)t
CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)
ORDER BY 1, 2


-- select * from ##tempList  -- prints unpivot data

if OBJECT_ID('tempdb..##tempList2') is not null
drop table ##tempList2

select ObjectName, ROW_NUMBER() over(partition by ObjectName order by ColumnName) columnseq, ColumnName
into ##templist2
from ##tempList

DECLARE
@tables NVARCHAR(MAX) = '',
@sql NVARCHAR(MAX) = '';
SELECT
@tables+=QUOTENAME(ObjectName) + ','
FROM
(select distinct ObjectName
from ##templist2
where 1=1
) t
ORDER BY ObjectName;

SET @tables = LEFT(@tables, LEN(@tables) - 1); 
SET @sql ='
SELECT  ' + @tables + '
FROM
(
select ObjectName, columnseq, ColumnName
from ##templist2
) t
PIVOT(
max(ColumnName)
FOR ObjectName IN ('+ @tables +')
) AS pivot_table;';

EXECUTE sp_executesql @sql;

score -1 · Answer 4 · answered Dec 11 '19 at 06:34

You can use string_split(), however it does not return a line number, though there are some workaround for that using row_number(). So i prefer to use Jeff Moden's DelimitedSplit8K which returns one. You can obtain the code from the link. DelimitedSplit8K parse the string returns ItemNumber and Item.

declare @sql    nvarchar(max)   = NULL

select  @sql    = isnull(@sql, 'SELECT ItemNumber' + char(13))
                + ',max(case when ObjectName = ''' + [ObjectName] + ''' then Item end) AS ' 
                + quotename(ObjectName) + char(13)
from    tbl

select  @sql    = @sql 
                + 'from  tbl t' + char(13)
                + 'cross apply DelimitedSplit8K(t.Whitelist, '','')' + char(13)
                + 'group by ItemNumber;'

-- print out the dynamic sql query
print   @sql
-- execute the dynamic query
exec    sp_executesql @sql

SQL How to display result as per below format

4 Answers4