CODEWITHSUNDEEP
sqlsql-server-2012pivot
300
300

Reputation: 1041

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

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."

Upvotes: 0

Views: 76

Answers (2)

SchmitzIT
SchmitzIT

Reputation: 9572

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;

Upvotes: 1

Jesse
Jesse

Reputation: 873

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

Upvotes: 1

Related Questions