Sushant Saurabh Sushant Saurabh - 3 months ago 8
SQL Question

convert rows into column

I have a table TableA and with 2 column A & B with entries

Table A

|A||B|

|A1|B1|

|A2|B2|

|A3|B3|

|A4|B4|

|A5|B5|


I want to split it in 10 column with different column name as

|E1|E2|E3|E4|E5|E6|E7|E8|E9|E10|

|A1|B1|A2|B2|A3|B3|A4|B4|A5|B5|


It's a part of a query that is associated with different tables that show the association with this table data.It fetches multiple row values that i want show in a single row.

Answer

You can do it with a dynamic sql query.

For the number sequence in the result set column names, we have to add an extra column.

Query

DECLARE @sql AS varchar(max);

SELECT @sql = 'select ' + STUFF((SELECT
    ',max(case A when ''' + A + ''' then ''' + A + ''' end) as E' 
        + CAST(E AS varchar(10)) +
    ',max(case B when ''' + B + ''' then ''' + B + ''' end) as E' 
        + CAST((E + 1) AS varchar(10))
  FROM (SELECT
    (ROW_NUMBER() OVER (
    ORDER BY A
    ) * 2 + 1) - 2 AS E, *
  FROM TableA) t
  FOR xml PATH (''))
  , 1, 1, '') + ' from TableA;';

EXEC (@sql);

Result

+====+====+====+====+====+====+====+====+====+=====+
| E1 | E2 | E3 | E4 | E5 | E6 | E7 | E8 | E9 | E10 |
+----+----+----+----+----+----+----+----+----+-----+
| A1 | B1 | A2 | B2 | A3 | B3 | A4 | B4 | A5 | B5  |
+====+====+====+====+====+====+====+====+====+=====+