SQL query which returns variable number of columns for each record combining multiple rows

I am working on an SQL query which should return data having same or different number of columns for each row

For example, I have the following data which comes from a CTE as follows

 With CTE 
    As(

    select distinct
    b.Acc_Num,
a.Type
    ,a.START
     ,a.END
    ,a.USAGE
    from USAGE_DETAIL a 
    join Table2 b on b.CUSTOMER_TKN=a.CUSTOMER_TKN
    )
    Select * from CTE Order By Acc_num

Result is

Acc_Num  Type  Start     End       Usage
100       A    1/1/10    1/1/15    100.00
100       A    3/1/10    3/1/15    200.00
100       A    2/1/10    2/1/15    300.00
200       A    1/1/10    1/1/15    150.00
200       A    3/1/10    3/1/15    250.00
300       A    1/1/10    1/1/15    1000.00
300       A    3/1/10    3/1/15    2000.00

In the above table, I have multiple records for each Acc_Num and I want to group all the records with same Acc_num and create additional columns depending on the number of records for each acc_num and result should like the following

100       A    1/1/10    1/1/15    100.00    3/1/10    3/1/15    200.00   2/1/10    2/1/15    300.00    
200       A    1/1/10    1/1/15    150.00    3/1/10    3/1/15    250.00
300       A    1/1/10    1/1/15    1000.00   3/1/10    3/1/15    2000.00

I am not sure if it is possible to return variable number of columns for a single set of data.

May I know a way to solve this?


ANSWERS:


Since you are pivoting multiple columns (3 in this series) in which dynamic SQL is required anyways, it may be easiest to just use dynamic SQL alone.

DECLARE @Max INT = (SELECT COUNT(*) FROM (SELECT DISTINCT Acc_Num, Type FROM Records) T)
DECLARE @Index INT = 0, @Sql VARCHAR(MAX) = ''
SET @Sql = @Sql + 'SELECT Acc_Num, Type'
WHILE @Index < @Max BEGIN
    SET @Index = @Index + 1
    DECLARE @IndexChar VARCHAR(10) = CONVERT(VARCHAR(10), @Index)
    SET @Sql = @Sql + ',MAX(CASE WHEN RowNum = ' + @IndexChar + ' THEN [Start] ELSE NULL END) AS [Start' + @IndexChar + ']'
    SET @Sql = @Sql + ',MAX(CASE WHEN RowNum = ' + @IndexChar + ' THEN [End] ELSE NULL END) AS [End' + @IndexChar + ']'
    SET @Sql = @Sql + ',MAX(CASE WHEN RowNum = ' + @IndexChar + ' THEN [Usage] ELSE NULL END) AS [Usage' + @IndexChar + ']'
END
SET @Sql = @Sql + ' FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY Acc_Num ORDER BY Start) AS RowNum FROM Records) T GROUP BY Acc_Num, Type'
EXEC(@Sql)

This results in:

Acc_Num     Type Start1     End1       Usage1                Start2     End2       Usage2                Start3     End3       Usage3
----------- ---- ---------- ---------- --------------------- ---------- ---------- --------------------- ---------- ---------- ---------------------
100         A    2010-01-01 2015-01-01 100.00                2010-02-01 2015-02-01 300.00                2010-03-01 2015-03-01 200.00
200         A    2010-01-01 2015-01-01 150.00                2010-03-01 2015-03-01 250.00                NULL       NULL       NULL
300         A    2010-01-01 2015-01-01 1000.00               2010-03-01 2015-03-01 2000.00               NULL       NULL       NULL

UPDATE

Based on you source data, I would just replace the sub-queries:

DECLARE @Max INT = (SELECT COUNT(*) FROM (
    SELECT DISTINCT
        b.Acc_Num,
        a.Type
    FROM USAGE_DETAIL a
        INNER JOIN Table2 b
            ON b.CUSTOMER_TKN=a.CUSTOMER_TKN
    ) T)
DECLARE @Index INT = 0, @Sql VARCHAR(MAX) = ''
SET @Sql = @Sql + 'SELECT Acc_Num, Type'
WHILE @Index < @Max BEGIN
    SET @Index = @Index + 1
    DECLARE @IndexChar VARCHAR(10) = CONVERT(VARCHAR(10), @Index)
    SET @Sql = @Sql + ',MAX(CASE WHEN RowNum = ' + @IndexChar + ' THEN [Start] ELSE NULL END) AS [Start' + @IndexChar + ']'
    SET @Sql = @Sql + ',MAX(CASE WHEN RowNum = ' + @IndexChar + ' THEN [End] ELSE NULL END) AS [End' + @IndexChar + ']'
    SET @Sql = @Sql + ',MAX(CASE WHEN RowNum = ' + @IndexChar + ' THEN [Usage] ELSE NULL END) AS [Usage' + @IndexChar + ']'
END
SET @Sql = @Sql + ' FROM (SELECT DISTINCT b.Acc_Num, a.Type, a.[START], a.[END], a.[USAGE], ROW_NUMBER() OVER (PARTITION BY b.Acc_Num ORDER BY a.[Start]) AS RowNum FROM USAGE_DETAIL a INNER JOIN Table2 b ON b.CUSTOMER_TKN=a.CUSTOMER_TKN) T GROUP BY Acc_Num, Type'
EXEC(@Sql)


 MORE:


 ? Convert row to columns SQL dynamically
 ? query to combine multiple columns in one columns
 ? Converting stored events into single records
 ? Pivot table multiple column values in a single column
 ? SQL Server - Dynamic Pivot
 ? SQL Server - Dynamic Pivot
 ? SQL Server - Dynamic Pivot
 ? Transform existing SQL into a pivot by column
 ? SQL Server Dynamic Pivot
 ? dynamic pivot issue in sql server 2012