Converting multiple rows to columns with heading from the same row

Question

I have a table that holds details for flats - contents of this table is similar to the following:

| flat    | description  | Amount   | Date    
--------------------------------------
| flat1 | electricity    | 1         |1/1/2016
| flat1 | water          | 2         |1/1/2016
| flat1 | levy           | 3         |1/1/2016
| flat2 | electricity    | 1         |1/1/2016
| flat2 | water          | 2         |1/1/2016
| flat2 | levy           | 3         |1/1/2016

I need a SQL view that can produce something like the following and any help would be appreciated:

| Flat  | electricity | water|levy | next description| and so on  | 
---------------------------------------------------------------
| flat1 | 1           | 2    | 3   | next amount     | next amount |
| flat2 | 1           | 2    | 3   | next amount     | next amount |

see my answer for a dynamic wa which allows you to filter on the dates — S3S, Jul 20 '16 at 19:23

score 1 · Accepted Answer · edited May 23 '17 at 12:32

1

This is called table pivoting. Here's one option using conditional aggregation assuming you know the number of potential columns:

select flat, 
    max(case when description = 'electricity' then amount end) electricity,
    max(case when description = 'water' then amount end) water,
    max(case when description = 'levy' then amount end) levy
from yourtable
group by flat

If you don't know the maximum number of columns, look up dynamic pivot -- there are lots of examples on how to do it:

https://stackoverflow.com/a/10404455/1073631

edited May 23 '17 at 12:32

Community

1
1

answered Jul 14 '16 at 21:12

sgeddes

62,311
6
61
83

thank you, it working on small database, when i use bigger database it keeps timing out.please assist. – Tinashe Jul 15 '16 at 23:27
@Tinashe -- You might benefit from adding an index on the flat, description and amount fields. As with most performance related questions though, it just depends on a lot of different factors. – sgeddes Jul 15 '16 at 23:56
Noted, how can i filter between two dates in the same table before the group by. – Tinashe Jul 17 '16 at 17:40

S3S · Answer 2 · 2016-07-20T19:25:21.413

drop table #temp
create table #temp (flat varchar(5), description varchar(128), amount int, date datetime)
insert into #temp (flat, description, amount, date) values
('flat1','electricity',1,'1/1/2016'),
('flat1','water',2,'1/1/2016'),
('flat1','levy',3,'1/1/2016'),
('flat2','electricity',1,'1/1/2016'),
('flat2','water',2,'1/1/2016'),
('flat2','levy',3,'1/1/2016')

select * from #temp


DECLARE @cols AS NVARCHAR(MAX),
        @query  AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(description) 
            FROM #temp
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT flat, ' + @cols + ' from 
            (select flat,amount, description from #temp) x
            pivot 
            (
                max(amount)
                for description in (' + @cols + ')
            ) p '


execute(@query)

You can add a date filter by placing a WHERE clause in teh @query portion. i.e. from #temp where date between '1/1/2016' and '3/1/2016'

Converting multiple rows to columns with heading from the same row

2 Answers2