Convert multiple columns into a single row [closed

Question

I have scenario where I need to change the present data to required one.

Below is the data

    ColumnA ColumnB ColumnC   
      P       Q       R   
      S       T       U   
      V       W       X

Required Format is

 ColumnA1 ColumnB1 ColumnC1 ColumnA2 ColumnB2 ColumnC2   ColumnA3   ColumnB3 ColumnC3    
   P         Q         R       S        T         U            V        W       x

Please help me on this.

Answer 1

There are a few ways that you can get the result all of them involve using row_number to generate a sequence for each row of your data.

You can use an aggregate function with a CASE expression:

select 
  max(case when seq = 1 then columna end) columna_1,
  max(case when seq = 1 then columnb end) columnb_1,
  max(case when seq = 1 then columnc end) columnc_1,
  max(case when seq = 2 then columna end) columna_2,
  max(case when seq = 2 then columnb end) columnb_2,
  max(case when seq = 2 then columnc end) columnc_2,
  max(case when seq = 3 then columna end) columna_3,
  max(case when seq = 3 then columnb end) columnb_3,
  max(case when seq = 3 then columnc end) columnc_3
from 
(
  select columna, columnb, columnc,
    row_number() over(order by columna) seq
  from yourtable
) d;

See SQL Fiddle with Demo.

You can use the PIVOT function, but you will want to unpivot your 3 columns of data first, then apply the PIVOT. The unpivot process converts your 3 columns of data into multiple rows. You can use the UNPIVOT function or CROSS APPLY to perform this:

select ColumnA_1, ColumnB_1, ColumnC_1,
  ColumnA_2, ColumnB_2, ColumnC_2,
  ColumnA_3, ColumnB_3, ColumnC_3
from
(
  select col = col+'_'+cast(seq as varchar(10)),
    value
  from
  (
    select columna, columnb, columnc,
      row_number() over(order by columna) seq
    from yourtable
  ) d
  cross apply
  (
    select 'ColumnA', columna union all
    select 'ColumnB', columnb union all
    select 'ColumnC', columnc
  ) c (col, value)
) s
pivot
(
  max(value)
  for col in (ColumnA_1, ColumnB_1, ColumnC_1,
              ColumnA_2, ColumnB_2, ColumnC_2,
              ColumnA_3, ColumnB_3, ColumnC_3)
) piv;

See SQL Fiddle with Demo. The above two versions work great if you have a limited number or known values, but if you will have an unknown number, then you will have to look at using dynamic SQL to get the final result:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(col+'_'+cast(seq as varchar(10))) 
                    from
                    (
                      select row_number() over(order by columna) seq
                      from yourtable
                    ) d
                    cross apply
                    (
                      select 'ColumnA', 1 union all
                      select 'ColumnB', 2 union all
                      select 'ColumnC', 3
                    ) c (col, so)
                    group by col, seq, so
                    order by seq, so
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')


set @query = 'SELECT ' + @cols + ' 
            from 
            (
              select col = col+''_''+cast(seq as varchar(10)),
                value
              from
              (
                select columna, columnb, columnc,
                  row_number() over(order by columna) seq
                from yourtable
              ) d
              cross apply
              (
                select ''ColumnA'', columna union all
                select ''ColumnB'', columnb union all
                select ''ColumnC'', columnc
              ) c (col, value)
            ) x
            pivot 
            (
                max(value)
                for col in (' + @cols + ')
            ) p '

execute sp_executesql @query;

See SQL Fiddle with Demo. All versions will give a result:

| COLUMNA_1 | COLUMNB_1 | COLUMNC_1 | COLUMNA_2 | COLUMNB_2 | COLUMNC_2 | COLUMNA_3 | COLUMNB_3 | COLUMNC_3 |
|         P |         Q |         R |         S |         T |         U |         V |         W |         X |