convert rows into column

Question

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.

What's the expected result if more rows are added to the table? — jarlh, Aug 19 '16 at 10:10
Have a look at http://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server — schlonzo, Aug 19 '16 at 10:13
Ms sql Pivot can not transpose more then 1 column at a time. And more , it needs to enumerate the values which enumeration must consist of all table values here . — Serg, Aug 19 '16 at 10:17
@schlonzo i have already seen that question what i'm trying to do is different . Please look at the question again. — Sushant Saurabh, Aug 19 '16 at 10:18

score 0 · Answer 1 · answered Aug 19 '16 at 10:28

I'm not quite sure, if I understand the content of your table right. Isn't there a primary key or something? I'm assuming that the content you showed is all we have:

SELECT a1.A AS E1, a1.B AS E2, a2.A AS E3, a2.B AS E4, a3.A AS E5, a3.B AS E6
    a4.A AS E7, a4.B AS E8, a5.A AS E9, a5.B AS E10
FROM TableA a1
JOIN TableA a2 ON a2.A = 'A2'
JOIN TableA a3 ON a3.A = 'A3'
JOIN TableA a4 ON a4.A = 'A4'
JOIN TableA a5 ON a5.A = 'A5'

If it's a variable number of rows I would do (asuming that the order of the columns doesn't matter):

SELECT A FROM TableA
UNION 
SELECT B FROM TableA

And afterwards use the already mentioned PIVOTfunction which SQL-Server provides

score 0 · Answer 2 · answered Aug 19 '16 at 10:30

For 5 rows exactly, otherwise build dynamic sql.

with t as (
  select *, rn=row_number() over(order by A,B) 
  from theTable
)
select 
    r1.A as E1, r1.B as E2, 
    r2.A as E3, r1.B as E4, 
    r1.A as E5, r1.B as E6, 
    r2.A as E7, r1.B as E8, 
    r5.A as E9, r5.B as E10
from t as r1 
join t as r2 on r2.rn=2
join t as r3 on r3.rn=3
join t as r4 on r4.rn=4
join t as r5 on r5.rn=5
where r1.rn=1

score 0 · Accepted Answer · answered Aug 19 '16 at 11:02

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  |
+====+====+====+====+====+====+====+====+====+=====+

convert rows into column

3 Answers3