How to display values in a single column into multiple and group by another column

Question

I have a database table "table_name1" in SQL Server 2012 created using:

CREATE TABLE table_name1 (
    created_date date,
    complete_hour int,
    col_percent float
);

INSERT INTO table_name1 values
('2017-06-14', 8, 0.3),
('2017-06-14', 9, 1.96),
('2017-06-14', 10, 3.92),
('2017-06-07', 8, 0.17),
('2017-06-07', 9, 2.87),
('2017-06-07', 10, 3.72),
('2017-05-31', 7, 0.14),
('2017-05-31', 8, 0.72),
('2017-05-31', 9, 3.77),
('2017-05-31', 10, 5.8);

What I want to do is get result like:

created_date    col1    col2    col3    col4
2017-06-14      BLANK   0.3     1.96    3.92
2017-06-07      BLANK   0.17    2.87    3.72
2017-05-31      0.14    0.72    3.77    5.8

I tried using pivot and as the number of rows in table_name1 will keep changing I think I'll have to use dynamic sql. So I tried using the answer from Efficiently convert rows to columns in sql server post but unable to tweak it to solve my problem. There are 3 columns instead of two that I need to consider and have to group by created_date as well.

Can I get some suggestions on how to do this?

EDIT: Little modified version of answer I am trying to follow is:

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

select @cols = STUFF((SELECT ',' + QUOTENAME(col_percent) 
                    from table_name1
                    group by created_date, complete_hour, col_percent
                    order by complete_hour
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = N'SELECT ' + @cols + N' from 
             (
                select created_date, col_percent
                from table_name1                
            ) x
            pivot 
            (
                max(created_date)
                for col_percent in (' + @cols + N')
            ) p '

exec sp_executesql @query;

And it gives result as:

0.14        0.72        0.17        0.3         3.77        2.87        1.96        5.8         3.72        3.92
2017-05-31  2017-05-31  2017-06-07  2017-06-14  2017-05-31  2017-06-07  2017-06-14  2017-05-31  2017-06-07  2017-06-14

I know I am doing it wrong to get my desired output, but when I try to change column names in the pivot, I get or some other changes I get either "invalid column name" or "The incorrect value "0.14" is supplied in the PIVOT operator."

Can you show us your version of the answer you referred to? And why it is not matching your desired output? — SchmitzIT, Jun 14 '17 at 18:49
I have edited question to include version of SQL that I am trying to tweak so it gives desired result. I don't think I understand it 100% but trying to. — 300, Jun 14 '17 at 19:09

score 1 · Answer 1 · answered Jun 14 '17 at 19:33

You can do a dynamic pivot to get what you want. Here's an example using a temp table from your example:

CREATE TABLE #table_name1 (
    created_date date,
    complete_hour int,
    col_percent float
);

INSERT INTO #table_name1 values
('2017-06-14', 8, 0.3),
('2017-06-14', 9, 1.96),
('2017-06-14', 10, 3.92),
('2017-06-07', 8, 0.17),
('2017-06-07', 9, 2.87),
('2017-06-07', 10, 3.72),
('2017-05-31', 7, 0.14),
('2017-05-31', 8, 0.72),
('2017-05-31', 9, 3.77),
('2017-05-31', 10, 5.8);


declare @sql nvarchar(max),
        @pvtColumns nvarchar(max),
        @selectColumns nvarchar(max)

select @pvtColumns = (
                        select ''+PivotColumns+','
                        from (
                            select distinct
                                    '['+convert(Varchar(10), complete_hour)+']' as PivotColumns, complete_hour
                            from #table_name1 
                            ) as b
                        order by complete_hour
                        for xml path('')
                     )

select @pvtColumns = substring(@pvtColumns,1,len(@pvtColumns)-1) 


set @sql = 

'
select 
    p.created_date,
    '+@pvtColumns+'
from        
    (
    select 
        created_date,
        complete_hour,
        col_percent
    from #table_name1 
    )
    as main
    pivot
    (
        max(col_percent)
        for complete_hour in ('+@pvtColumns+')
    ) as p
order by 
    created_date
'

exec sp_Executesql @sql

Thank you for the response Jesse. I wish I could mark more than one replies as answer. Your solution works perfect for me as well but I can't mark it as answer in addition to SchmitzIT. My apologies for that. — 300, Jun 14 '17 at 20:26

SchmitzIT · Accepted Answer · 2017-06-14T19:58:12.560

If we take things one step at a time, let's try and do this first without using dynamic sql.

I believe this query yields the results you are looking for:

SELECT created_date, [7] AS col1, [8] AS col2, [9] AS col3, [10] AS col4
FROM 
    (
        select created_date, complete_hour, col_percent
        from table_name1                
    ) x
    pivot 
    (
        max(col_percent)
        for complete_hour in ([7],[8],[9],[10])
    ) p 
    ORDER BY created_date DESC

Output:

created_date    col1    col2    col3    col4
2017-06-14      NULL    0,3     1,96    3,92
2017-06-07      NULL    0,17    2,87    3,72
2017-05-31      0,14    0,72    3,77    5,8

**** Update: OP confirmed results look correct. Now for some dynamic sql ninja-stuff

To make this a bit more dynamic, the following would work:

We start by declaring the two variables that will hold the columns and the query:

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

Next up, we determine the columns we want to grab from the table. In our case, this is the complete_hour. Seeing these most likely repeat over several days, and we only want them once, we GROUP BY complete_hour:

SELECT @cols = STUFF((SELECT ',' + QUOTENAME(complete_hour) 
                    from table_name1
                    group by complete_hour
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

We can now test what is in our variable:

PRINT @cols

Based on the test data provided, currently it would contain

[7],[8],[9],[10]

In real life, this would be a value for every distinct complete_hour value.

On-wards with building the query:

set @query = N'SELECT created_date, ' + @cols + N' from 
             (
                select created_date, complete_hour, col_percent
                from table_name1                
            ) x
            pivot 
            (
                max(col_percent)
                for complete_hour in (' + @cols + N')
            ) p 
            ORDER BY created_date DESC
        '

As you want the created_date column, that needs to be in the SELECT statement. We also want every value of complete_hour, which is what we stored in @cols.

We actually want to grab everything, so we select all three columns, and then pivot the col_percent for every complete_hour.

Lastly, we sort by created_date, with the latest date showing first.

We can then execute:

exec sp_executesql @query;

@300 Awesome. So am I correct in assuming there are more columns you'd need? That might get somewhat tricky with naming them, but we can give it a shot. However, the query above can be used as the basis for creating the dynamic sql you're after. — SchmitzIT, Jun 14 '17 at 19:41
Yes, I would need more columns but they'll be between 6 to 23. But I won't know number of rows that would be present in the table_name1 — 300, Jun 14 '17 at 19:48
@300 - Check the update. I believe this would cover your needs. If you don't understand something, just let me know :) — SchmitzIT, Jun 14 '17 at 19:58
Thank you SchmitzIT. yes it gives exactly what I needed. And I can understand what I am going to use so I am marking your response as answer. — 300, Jun 14 '17 at 20:24

How to display values in a single column into multiple and group by another column

2 Answers2