How To Display Row Value As Column Value In Sql Server (only One Column Rows Value Should Be Displayed As Multiple Columns)

Using join of parent and child tables I am getting results like this select a.id, a.pname, b.childname from parentinfo a right join childinfo b on a.i

Solution 1:

Here is one approach using dynamic crosstab:

SQL Fiddle

Generate sample data

use tempdb;
CREATETABLE yourtable(
    id          INT,
    pname       VARCHAR(20),
    childname   VARCHAR(20)
)
INSERTINTO yourtable VALUES
(1, 'Parent1', 'p1child1'), 
(1, 'Parent1', 'p1child2'), 
(1, 'Parent1', 'p1child3'), 
(2, 'Parent2', 'p2child1'), 
(2, 'Parent2', 'p2child2'), 
(3, 'Parent3', 'p3child1'), 
(3, 'Parent3', 'p3child2'), 
(3, 'Parent3', 'p3child3'), 
(3, 'Parent3', 'p3child4'), 
(4, 'Parent4', 'p4child1'), 
(4, 'Parent4', 'p4child2'), 
(4, 'Parent4', 'p4child3');

Dynamic Crosstab

DECLARE@maxNoChildrenINTDECLARE@sql1VARCHAR(4000) =''DECLARE@sql2VARCHAR(4000) =''DECLARE@sql3VARCHAR(4000) =''SELECT TOP 1@maxNoChildren=COUNT(*) FROM yourtable GROUPBY id ORDERBYCOUNT(*) DESCSELECT@sql1='SELECT
    id
    ,pname
'SELECT@sql2=@sql2+'   ,MAX(CASE WHEN RN = '+CONVERT(VARCHAR(5), N) +' THEN childname END) AS '+ QUOTENAME('child'+CONVERT(VARCHAR(5), N)) +CHAR(10)
FROM(
    SELECT TOP(@maxNoChildren)
        ROW_NUMBER() OVER(ORDERBY (SELECTNULL))
    FROM sys.columns a
    --CROSS JOIN sys.columns b
)T(N)
ORDERBY N

SELECT@sql3='FROM(
    SELECT *,
        RN = ROW_NUMBER() OVER(PARTITION BY id ORDER BY (SELECT NULL))
    FROM yourtable
)t
GROUP BY id, pname
ORDER BY id'

PRINT(@sql1+@sql2+@sql3)
EXEC (@sql1+@sql2+@sql3)

Result

| id |   pname |   child1 |   child2 |   child3 |   child4 ||----|---------|----------|----------|----------|----------||1| Parent1 | p1child1 | p1child2 | p1child3 |   (null) ||2| Parent2 | p2child1 | p2child2 |   (null) |   (null) ||3| Parent3 | p3child1 | p3child2 | p3child3 | p3child4 ||4| Parent4 | p4child1 | p4child2 | p4child3 |   (null) |