Rows to Columns RDLC

Question

i have data in below format. this data is coming through SQL Query.

i want to show it in below format either by query or by rdlc report.

Answer 1

You need to use dynamic SQL to make it.

From your expected result you can try to follow thoes step to make it.

1. use row_number function make row number by Name, because we need to join base on that row_number.

2. get the use MAX and MIN to make row number calendar table. from 1 to max(rn). the table can let use outer join

3. declare a var @tables to make the OUTER JOIN execute SQL (each LEFT JOIN maen a group of Crew#).

4. declare a var @col to make column, which you want to select (Employee) from each table.

5. then use execute dynamic execute it.

look like this.

create table T
(
    Name varchar(50),
    Employee VARCHAR(50)
)

insert into T values ('Crew#1','TR123');
insert into T values ('Crew#1','311');
insert into T values ('Crew#2','DDD');
insert into T values ('Crew#2','12121');
insert into T values ('Crew#1','SDDAS');
insert into T values ('Crew#3','31114312');
insert into T values ('Crew#3','DD14124D');
insert into T values ('Crew#3','1214124121');
insert into T values ('Crew#3','SDD412AS');


DECLARE @tables AS NVARCHAR(MAX),
        @query  AS NVARCHAR(MAX),
        @col AS NVARCHAR(MAX);



SET @tables = STUFF((SELECT distinct ' LEFT JOIN ' + ' (SELECT * FROM CTE WHERE Name = '''+Name+''') '+QUOTENAME(Name)+' on t1.smallRN = '+QUOTENAME(Name)+'.rn' 
            FROM T
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')


SET @col = STUFF((SELECT distinct ', ' + QUOTENAME(Name)+'.Employee as '''+ QUOTENAME(Name) +''''
            FROM T
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

SET @col = substring(@col,1, len(@col))

set @query = '
WITH CTE AS (
    SELECT *,ROW_NUMBER() OVER(PARTITION BY Name ORDER BY Name) rn
    FROM T 
),CTE1 AS(
    SELECT MIN(rn) smallRN,MAX(rn) bigRN
    FROM CTE 
    UNION ALL
    SELECT smallRN+1,bigRN
    FROM CTE1
    WHERE smallRN < bigRN
)
SELECT '+@col+' 
FROM CTE1 t1 ' + @tables

execute(@query)

sqlfiddle