CODEWITHSUNDEEP
sqlsql-serverpivotpivot-table
Santosh Jadi
Santosh Jadi

Reputation: 1527

Convert all Rows to Columns in SQL Server

Trying to Convert Rows to Multiple columns in SQL Server as show below:

Current result: 

PortCode    CarCode
------------------------
AAB         A5
ADR         BH
SAN         QQ

Expected result: 

PortCode   CarCode   PortCode  CarCode   PortCode   CarCode
-----------------------------------------------------------
AAB        A5        ADR       BH        SAN        QQ

Tried withPIVOT but didn't helped.

Can anyone please explain me, how to achieve it?

Upvotes: 0

Views: 104

Answers (2)

Ullas
Ullas

Reputation: 11556

If you want to make it dynamic, you can use the following sql query.

Query

declare @sql as varchar(max);

select @sql = 'select ' + stuff((
    select distinct ',max(case [PortCode] when ' + char(39) + [PortCode] + char(39) + 
    ' then [PortCode] end) as [PortCode]'
    + ',max(case [CarCode] when ' + char(39) + [CarCode] + char(39) + 
    ' then [CarCode] end) as [CarCode]'
    from [your_table_name]
    for xml path('')
), 1, 1, '');

select @sql += ' from [your_table_name];';
exec(@sql);

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270713

If I understand correctly,

select max(case when seqnum = 1 then portcode end) as portcode_1,
       max(case when seqnum = 1 then carcode end) as carcode_1,
       max(case when seqnum = 2 then portcode end) as portcode_2,
       max(case when seqnum = 2 then carcode end) as carcode_2,
       max(case when seqnum = 3 then portcode end) as portcode_3,
       max(case when seqnum = 3 then carcode end) as carcode_3       
from (select t.*, row_number() over (order by (select null)) as seqnum
      from t
     ) t;

Notes:

  • This is not dynamic. It produces 6 columns (but you can use the same idea for a dynamic query).
  • The ordering of the results is indeterminate. SQL tables represent unordered sets. If you want the columns in a particular order, then replace (select null) with the appropriate column.

Upvotes: 1

Related Questions