Click here to Skip to main content
16,016,613 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
logTable
=================
BATCH DATE EXEC-DURATION
--------------------------------
B1 1/JAN/2017 80 Sec
B2 1/JAN/2017 40 Sec
B1 2/JAN/2017 20 Sec
B2 2/JAN/2017 50 Sec
.
.
.

o/p:

BATCH 1/JAN 2/JAN .......
B1 80 20
B2 40 50

What I have tried:

Hi i want output like above. can one give query for this?
Posted
Updated 30-Oct-17 20:14pm
Comments
CHill60 29-Oct-17 3:48am    
What have you tried?
What is the schema of the "table"?

You can dynamically generate columns.following is code

SQL
Create table logtable
(

batch  varchar(5),
date1  DateTime,
Duration  integer)

Insert into logtable values('B1','01-jAN-2017',80),
('B2','01-jAN-2017',40),
('B1','02-jAN-2017',20),
('B2','02-jAN-2017',50)


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

select @cols = STUFF((SELECT ',' + QUOTENAME(DATE1) 
                    from logtable 
                    group by date1 
                    order by DATE1
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT BATCH,' + @cols + ' from 
             (
                select BATCH, DATE1, DURATION
                from LOGTABLE
            ) x
            pivot 
            (
                sum(DURATION)
                for DATE1 in (' + @cols + ')
            ) p '

execute(@query);
 
Share this answer
 
Not really, no.
The problem is that the number of columns you would return changes: sometimes 29, sometimes 30, 31, or 32 depending on the month and year. And PIVOT (which is what you want to use) only works with fixed column counts (because you have to explicitly specify the columns to use).

I'd strongly suggest that you work on this in your presentation code, not SQL.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900