Transpose rows in SQL Server

Question

I have an Address table as below

CREATE TABLE ADDRESS
(
    PERSON_ID INT,
    ADDRESSLINE1 VARCHAR(100),  
    CITY VARCHAR(100)
)

Data in the table is as below

INSERT INTO ADDRESS 
VALUES (1, 'Street 1', 'CITY1'), (1, 'Street 2', 'CITY1'),
       (1, 'Street 3', 'CITY1'),
       (2, 'Street 1', 'CITY2'), (2, 'Street 2', 'CITY2'),
       (2, 'Street 3', 'CITY3'),
       (3, 'Street 1', 'CITY3'), (3, 'Street 2', 'CITY3')

I need to write a SQL query that returns an output in the below format

PERSON_ID   ADDRESS1                ADDRESS2            ADDRESS3
------------------------------------------------------------------------
1           'Street 1 CITY1'        'Street 2 CITY1'    'Street 3 CITY1'
2           'Street 1 CITY2'        'Street 2 CITY2'    'Street 3 CITY2'
3           'Street 1 CITY3'        'Street 2 CITY3'

The address data (ADDRESSLINE1 and CITY) needs to be concatenated into a single value, and the resultset should have a column for each row in the table. Data needs to grouped based on PERSON_ID

Can this be done using a SQL statement?

Possible duplicate of [Efficiently convert rows to columns in sql server](https://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server) — Thom A, Jun 25 '18 at 09:22
Apart from the value (which in a real world scenario scenario would not be true), what denotes that `'Street 2','CITY1'` is the value for `ADDRESS2`? For example, what if you had the values `(4,'17 Long Road','BigCity'),(4,'12 Short Street','Little Village'),(4,'15 Main Road','Large Town')`. Which is `ADDRESS1`, which is `ADDRESS2` and which is `ADDRESS3`? — Thom A, Jun 25 '18 at 09:38
There is nothing that specifies this. The address columns (ADDRESSLINE1 and CITY) need to be concatenated and each address should be shown in a column. Yes its possible to have ADDRESS4 and additional ones as well. — Sam Jones, Jun 25 '18 at 09:51
So the order in which they appear doesn't matter, and it doesn't matter if it changes if run at a later date? — Thom A, Jun 25 '18 at 09:54
Thats correct. Order does not matter and it doesnt matter if the order changes every time the query runs. — Sam Jones, Jun 25 '18 at 09:56

Chanukya · Answer 1 · 2018-06-25T09:31:49.393

0

CREATE TABLE ADDRESS(
    PERSON_ID INT,
    ADDRESSLINE1 VARCHAR(100),  
    CITY VARCHAR(100))

INSERT INTO ADDRESS VALUES(1,'STREET 1','CITY1')
INSERT INTO ADDRESS VALUES(1,'STREET 2','CITY1')
INSERT INTO ADDRESS VALUES(1,'STREET 3','CITY1')
INSERT INTO ADDRESS VALUES(2,'STREET 1','CITY2')
INSERT INTO ADDRESS VALUES(2,'STREET 2','CITY2')
INSERT INTO ADDRESS VALUES(2,'STREET 3','CITY3')
INSERT INTO ADDRESS VALUES(3,'STREET 1','CITY3')
INSERT INTO ADDRESS VALUES(3,'STREET 2','CITY3')





SELECT PERSON_ID, MAX(CASE WHEN ADDRESSLINE1='STREET 1' THEN CONCAT(ADDRESSLINE1,' ',CITY)  ELSE ''  END)  ADDRESS1  ,
                  MAX(CASE    WHEN ADDRESSLINE1='STREET 2' THEN CONCAT(ADDRESSLINE1,' ',CITY) ELSE ''  END)  ADDRESS2  ,
                  MAX(CASE   WHEN ADDRESSLINE1='STREET 3' THEN CONCAT(ADDRESSLINE1,' ',CITY) ELSE ''   END)  ADDRESS3  FROM ADDRESS
          GROUP BY PERSON_ID 


output

PERSON_ID   ADDRESS1                ADDRESS2            ADDRESS3
1           'STREET 1 CITY1'        'STREET 2 CITY1'    'STREET 3 CITY1'
2           'STREET 1 CITY2'        'STREET 2 CITY2'    'STREET 3 CITY2'
3           'STREET 1 CITY3'        'STREET 2 CITY3'

edited Jun 25 '18 at 09:31

answered Jun 25 '18 at 09:27

Chanukya

5,833
1
22
36

I think the samples wouldnt justify a real solution. I guess that addresslines dont start with street 1,2,3 etc. – SqlKindaGuy Jun 25 '18 at 09:31
1

@Thomas NOw what are you expecting whether this is not correct answer you are saying as per the given sample data i wrote code like this – Chanukya Jun 25 '18 at 09:33
I am just saying. Your example meets the fully requirements of the sampledata. But if one think out of the box i dont think it will. But it was also only just an information :) – SqlKindaGuy Jun 25 '18 at 09:34
Thomas is right Chanukya. I've just used Street 1 etc as examples.In real world sceanio these values can be some valid street addresses. – Sam Jones Jun 25 '18 at 09:46

score 0 · Answer 2 · answered Jun 25 '18 at 09:53

This here will solve you problem - Ive used dynamic Pivoting to solve it.

NB: Ived change your Addressline1 to be named Addressline instead on the table

NB2: This is dynamic which means i count how many appearance there is of one ID. If there is 4 apperance on one ID then you will get 4 columns with Addressline.

CREATE TABLE ADDRESS(
    PERSON_ID INT,
    ADDRESSLINE VARCHAR(100),  
    CITY VARCHAR(100))


DECLARE @Str NVARCHAR(MAX);
DECLARE @Str2 NVARCHAR(MAX);
SELECT @Str = STUFF(
                   (
                       SELECT DISTINCT
                              ','+QUOTENAME(Addresses)
                       FROM ( 

select 'Addressline'+cast(n as varchar(50)) as Addresses from (select max(totalcountaddresses) as TotalAddresses from (select COUNT(*) as TotalCountAddresses from dbo.address
group by person_id
)x
)o
cross apply (
select n from 
(select ROW_NUMBER() over(PARTITION by name order by (select null)) as n from master..spt_values where type='p' 
)z
where n <= o.TotalAddresses
)y
)t

                       FOR XML PATH('')
                   ), 1, 1, '');
-- PRINT @Str
SET @str2 = N'
select * from (
select AddressLine,Person_Id,''Addressline''+cast(rn as varchar(50)) as Adr  from (
   select Addressline+ '' '' +city as Addressline,Person_id,ROW_NUMBER() over(PARTITION by Person_id order by addressline) as rn
   from dbo.address
   )z
   )x
   PIVOT 
   (Max(Addressline) FOR adr in ('+@Str+')
   ) as p';
--PRINT @Str2;
EXEC (@Str2);

Result

NB: You can just change Addressline to Address if you want other headernames :) — SqlKindaGuy, Jun 25 '18 at 10:30

LukStorms · Accepted Answer · 2018-06-25T13:46:43.127

You can PIVOT this.

The trick is to use ROW_NUMBER to generate the column names for the PIVOT.

SELECT * 
FROM
(
    SELECT 
     PERSON_ID, 
     CONCAT(ADDRESSLINE1,' ',CITY) AS ADDRESS_CITY, 
     CONCAT('ADDRESS',ROW_NUMBER() OVER (PARTITION BY PERSON_ID ORDER BY ADDRESSLINE1)) AS COL
    FROM ADDRESS
) SRC
PIVOT 
(
    MAX([ADDRESS_CITY])
    FOR COL IN ([ADDRESS1],[ADDRESS2],[ADDRESS3])
) AS PVT
ORDER BY PERSON_ID;

To do this the Dynamic Sql way, up to 42 ADDRESS columns:

declare @Cols varchar(535);
declare @MaxTotalAddresses INT = (select top 1 count(*) from ADDRESS group by PERSON_ID order by count(*) desc);

;with RCTE as
(
    select 1 as n
    union all
    select n + 1 from RCTE where n < @MaxTotalAddresses
)
select @Cols = concat(@Cols + ', ', quotename(concat('ADDRESS',n)))
from RCTE;

declare @DynSql varchar(1000);
set @DynSql = 'SELECT * 
FROM
(
    SELECT 
     PERSON_ID, 
     CONCAT(ADDRESSLINE1,'' ''+CITY) AS ADDRESS_CITY, 
     CONCAT(''ADDRESS'',ROW_NUMBER() OVER (PARTITION BY PERSON_ID ORDER BY ADDRESSLINE1)) AS COL
    FROM ADDRESS
) SRC
PIVOT 
(
    MAX([ADDRESS_CITY])
    FOR COL IN ('+ @Cols +')
) AS PVT
ORDER BY PERSON_ID';

--select @DynSql as DynSql;
exec(@DynSql);

Transpose rows in SQL Server

3 Answers3