sql - convert rows to column

Question

I have the following table structure:

ID  ADDRESS                     SALEPRICE   PRICEPERAREA    APPRAISALREPORTID
1       2560 W Central Ave      115000      98.46                   1
2       543 N Logan             110000      94.18                   1
3       321 Wall Street         115000      98.46                   1
4       5441 N East Road        125000      94.65                   2
5       2635 N Califnia Ave     118000      92.35                   2
6       1526 W 18th Place       12000       91.54                   2

I want the output to look like this:

Here I am not converting values to columns instead i want values will be part of the record.

I have checked multiple articles about pivot & cross apply but not able to understand how it will help me here.

Some fatcs about the data:

For each APPRAISALRECORD there will be always 3 records. So the count of column in output will be always 3x3=9

Convert Rows to columns using 'Pivot' in SQL Server

score 0 · Accepted Answer · answered Aug 07 '18 at 19:13

You can try to make Row_number with window function in subquery or CTE then do pivot by Condition Aggregate function.

TestDLL

CREATE TABLE T(
 ID INT,
  ADDRESS VARCHAR(50),
  SALEPRICE INT,
  PRICEPERAREA FLOAT,
  APPRAISALREPORTID INT
);


INSERT INTO T VALUES (1,'2560 W Central Ave ',115000,98.46,1);
INSERT INTO T VALUES (2,'543 N Logan',110000,94.18,1);                   
INSERT INTO T VALUES (3,'321 Wall Street',115000,98.46,1);
INSERT INTO T VALUES (4,'5441 N East Road',125000,94.65,2);
INSERT INTO T VALUES (5,'2635 N Califnia Ave',118000,92.35,2);
INSERT INTO T VALUES (6,'1526 W 18th Place',12000 ,91.54, 2);

Query 1:

with cte as(
  SELECT *,ROW_NUMBER() OVER(PARTITION BY APPRAISALREPORTID ORDER BY ID) rn
   FROM T
)

SELECT APPRAISALREPORTID,
      MAX(CASE WHEN rn = 1 then ADDRESS end) 'ADDRESS1',  
      MAX(CASE WHEN rn = 1 then SALEPRICE end) 'SALEPRICE1',  
      MAX(CASE WHEN rn = 1 then PRICEPERAREA end) 'PRICEPERAREA1',
      MAX(CASE WHEN rn = 2 then ADDRESS end) 'ADDRESS2',  
      MAX(CASE WHEN rn = 2 then SALEPRICE end) 'SALEPRICE2',  
      MAX(CASE WHEN rn = 2 then PRICEPERAREA end) 'PRICEPERAREA2',
      MAX(CASE WHEN rn = 3 then ADDRESS end) 'ADDRESS3',  
      MAX(CASE WHEN rn = 3 then SALEPRICE end) 'SALEPRICE3',  
      MAX(CASE WHEN rn = 3 then PRICEPERAREA end) 'PRICEPERAREA3'
FROM CTE
group by APPRAISALREPORTID

Results:

| APPRAISALREPORTID |            ADDRESS1 | SALEPRICE1 | PRICEPERAREA1 |            ADDRESS2 | SALEPRICE2 | PRICEPERAREA2 |          ADDRESS3 | SALEPRICE3 | PRICEPERAREA3 |
|-------------------|---------------------|------------|---------------|---------------------|------------|---------------|-------------------|------------|---------------|
|                 1 | 2560 W Central Ave  |     115000 |         98.46 |         543 N Logan |     110000 |         94.18 |   321 Wall Street |     115000 |         98.46 |
|                 2 |    5441 N East Road |     125000 |         94.65 | 2635 N Califnia Ave |     118000 |         92.35 | 1526 W 18th Place |      12000 |         91.54 |

score 0 · Answer 2 · answered Aug 07 '18 at 19:40

Another option (also using ROW_NUMBER, but arguably a bit cleaner):

with 

-- this is just to generate the test data. You'd use your actual table instead.
Data(ID, Address, SalePrice, PricePerArea, AppraisalReportID) as (
    select 1, '2560 W Central Ave', 115000, 98.46, 1
    union
    select 2, '543 N Logan', 110000, 94.18, 1
    union
    select 3, '321 Wall Street', 115000, 98.46, 1
    union
    select 4, '5441 N East Road', 125000, 94.65, 2
    union
    select 5, '2365 N Califnia Ave', 118000, 92.35, 2
    union
    select 6, '1526 W 18th Place', 12000, 91.54, 2
)

-- you don't need this CTE if you have another table somewhere that stores Appraisal Report IDs
,AppraisalReports([id]) as (select distinct AppraisalReportID from Data)

-- this just gives you your table data + ROW_NUMBER values
,DataWithRowNum(id, Address, SalesPrice, PricePerArea, AppraisalReportID, rn) as (
    select *, ROW_NUMBER() over (partition by AppraisalReportID order by ID)
    from Data
)

-- the actual query, using ROW_NUMBER values for 3 separate joins
select
    AppraisalReports.id AppraisalReportID
    -- Record 1
    ,Record1.Address Address1
    ,Record1.SalesPrice SalesPrice1
    ,Record1.PricePerArea PricePerArea1
    -- Record 2
    ,Record2.Address Address2
    ,Record2.SalesPrice SalesPrice2
    ,Record2.PricePerArea PricePerArea2
    -- Record 3
    ,Record3.Address Address3
    ,Record3.SalesPrice SalesPrice3
    ,Record3.PricePerArea PricePerArea3
from AppraisalReports
    inner join DataWithRowNum Record1 on
        Record1.AppraisalReportID = AppraisalReports.id
        and Record1.rn = 1
    inner join DataWithRowNum Record2 on
        Record2.AppraisalReportID = AppraisalReports.id
        and Record2.rn = 2
    inner join DataWithRowNum Record3 on
        Record3.AppraisalReportID = AppraisalReports.id
        and Record3.rn = 3

gives you this result:

+-------------------+--------------------+-------------+---------------+---------------------+-------------+---------------+-------------------+-------------+---------------+
| AppraisalReportID |      Address1      | SalesPrice1 | PricePerArea1 |      Address2       | SalesPrice2 | PricePerArea2 |     Address3      | SalesPrice3 | PricePerArea3 |
+-------------------+--------------------+-------------+---------------+---------------------+-------------+---------------+-------------------+-------------+---------------+
|                 1 | 2560 W Central Ave |      115000 |         98.46 | 543 N Logan         |      110000 |         94.18 | 321 Wall Street   |      115000 |         98.46 |
|                 2 | 5441 N East Road   |      125000 |         94.65 | 2365 N Califnia Ave |      118000 |         92.35 | 1526 W 18th Place |       12000 |         91.54 |
+-------------------+--------------------+-------------+---------------+---------------------+-------------+---------------+-------------------+-------------+---------------+

sql - convert rows to column

2 Answers2