CODEWITHSUNDEEP
sqlsql-servert-sqldb2transpose
Rohit Kishore Gupta
Rohit Kishore Gupta

Reputation: 21

Multiple Rows into One Row multiple columns

We need to list all numbers as a flat data set, how can we do that?

Table Name: Telephone

ID      TYPE      NUMBER
==================================
123      MN       042153939
123      HN       2242116
123      MN       1234567890
123      HN       12345678

Create Table Telephone
(
  ID Integer,
  Type char(3),
  Number Varchar(20)
);

insert into Telephone values 
(123, 'MN', '042153939'),
(123, 'HN', '2242116'),
(123, 'MN', '1234567890'),
(123, 'HN', '12345678');

I want SQL to return data in this format

ID    MN#1       Mn#2          HN#1     HN#2
================================================
123   042153939  1234567890   2242116   12345678

Upvotes: 1

Views: 12435

Answers (4)

Vitaly Borisov
Vitaly Borisov

Reputation: 1193

Dynamic approach

Init

DROP TABLE IF EXISTS #Telephone;
CREATE TABLE #Telephone(ID INT,Type CHAR(3),Number VARCHAR(20));
INSERT INTO #Telephone (ID,Type,Number) VALUES 
(123, 'MN', '042153939'),
(123, 'HN', '2242116'),
(123, 'MN', '1234567890'),
(123, 'HN', '12345678');

The code

DECLARE @ColumnList NVARCHAR(MAX);
SELECT @ColumnList = STUFF((SELECT ',[' + RTRIM(t.[Type]) + '#' 
                + CONVERT(NVARCHAR(255),ROW_NUMBER()OVER(PARTITION BY t.[Type] ORDER BY t.ID)) + ']'
                FROM #Telephone t FOR XML PATH(''),TYPE).value('(./text())[1]','NVARCHAR(MAX)'),1,1,'')
;
DECLARE @sql NVARCHAR(MAX) = '';
SET @sql = N'
SELECT ID,' + @ColumnList + N'
FROM (
    SELECT t.ID,t.Number, RTRIM(t.[Type]) + ''#'' + CONVERT(NVARCHAR(255),ROW_NUMBER()OVER(PARTITION BY t.[Type] ORDER BY t.ID)) AS [Type]
    FROM #Telephone t
) a
PIVOT(MAX(a.Number) FOR a.Type IN (' + @ColumnList + N')) p
'
;
--PRINT @sql
IF @sql IS NOT NULL EXEC(@sql);

Upvotes: 1

DarkRob
DarkRob

Reputation: 3833

You may try this. with row_number() and pivot.
For more info about pivot you may find this link PIVOT.

  ; with cte as (
        select row_number() over (partition by type order by id ) as Slno, * from Telephone 
   )
   , ct as (
        select id, type + '#' + cast(slno as varchar(5)) as Type, values from cte
   )
   select * from (
        select * from ct
   ) as d
   pivot 
   ( max(values) for type in ( [MN#1],[Mn#2],[HN#1],[HN#2] )
   ) as p

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521103

We can try using a pivot query with the help of ROW_NUMBER():

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY TYPE DESC, NUMBER) rn
    FROM Telephone
)

SELECT
    ID,
    MAX(CASE WHEN rn = 1 THEN NUMBER END) AS [MN#1],
    MAX(CASE WHEN rn = 2 THEN NUMBER END) AS [MN#2],
    MAX(CASE WHEN rn = 3 THEN NUMBER END) AS [HN#3],
    MAX(CASE WHEN rn = 4 THEN NUMBER END) AS [HN#4]
FROM cte
GROUP BY ID;

Upvotes: 0

Avinash Sharma
Avinash Sharma

Reputation: 653

try pivoting like below :

     SELECT first_column AS <first_column_alias>,
     [pivot_value1], [pivot_value2], ... [pivot_value_n]
     FROM
     (<source_table>) AS <source_table_alias>
     PIVOT
     (
     aggregate_function(<aggregate_column>)
     FOR <pivot_column> IN ([pivot_value1], [pivot_value2], ... [pivot_value_n])
     ) AS <pivot_table_alias>;

Upvotes: 0

Related Questions