CODEWITHSUNDEEP
sql-servert-sqlpivot
Dragos Durlut
Dragos Durlut

Reputation: 8098

How to get a report with dynamic dates as columns in T-SQL

How to get a report with dynamic dates as columns in T-SQL.

I was looking for a piece of code like this like crazy.

Hope it will help other people.

If anyone knows any better solution please post.

DECLARE @columns VARCHAR(8000)

SELECT @columns = COALESCE(@columns + ',[' + convert(nvarchar(12), data, 103) + ']',
'[' + convert(nvarchar(12), data, 103)+ ']')
FROM Item
GROUP BY data
--print @columns


DECLARE @query VARCHAR(8000)

SET @query = '

;WITH G1 As
(
select id_item, convert(nvarchar(12), data, 103) as data, COUNT(*) as numar
from Item
group by id_item, data

)

SELECT *
FROM G1
PIVOT
(
sum(numar)
FOR [data]
IN (' + @columns + ')
) AS G2
'
print @query
EXECUTE(@query)

Upvotes: 2

Views: 540

Answers (2)

Dragos Durlut
Dragos Durlut

Reputation: 8098

Here is a static version (from a coleague):

with c as (
    select
        p.id_doc,
        p.id_usr,
        DATEDIFF(DAY, p.created_at, getdate()) as date_diff
    from Document p
    where 1 = 1 
), p as (
    select
        pvt.id_usr,
        [0], [1], [2], [3], [4], [5], [6], [7], [8], [9]
    from c
    PIVOT (COUNT(c.id_doc) FOR c.date_diff IN ([0], [1], [2], [3], [4], [5], [6], [7], [8], [9])) AS pvt
)
select
    u.username,
    u.name,
    [0], [1], [2], [3], [4], [5], [6], [7], [8], [9],
    y.nr,
    y.total_money
from p
inner join Users u on u.id_usr = p.id_usr
cross apply (
    select
        COUNT(1) as nr,
        SUM(premium) as total_money
    from Document z
    where z.id_usr = p.id_usr
) as y
order by nr desc

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 453327

A couple of comments on the example in your question.

Use QUOTENAME rather than concatenating the square brackets yourself. This will deal correctly with any ] characters in the data.

You seem to be mixing nvarchar and varchar. Declare the dynamic SQL variables as NVARCHAR(MAX) (or NVARCHAR(4000) if targeting SQL Server 2000) not VARCHAR(8000). This will mean that your query works correctly with Unicode data and is a generally more secure practice when concatenating dynamic SQL.

(Just to add an example of why I say this is more secure)

create table #t (a int, b char(1));

DECLARE @querystring varchar(max)
DECLARE @nquerystring nvarchar(max)
DECLARE @userinput nvarchar(100) = N'ʼ;Drop Table #t--'

IF @userinput LIKE '%''%'
        BEGIN
        RAISERROR('Possible SQL injection',16,1)
        RETURN
        END


SET @nquerystring = 'select * from #t where b = ''' + @userinput + ''''


exec (@nquerystring)

select OBJECT_ID('tempdb..#t') /*NOT NULL, Still There*/

SET @querystring = @nquerystring /*ʼ nchar(700) gets silently converted to a regular ' character*/

exec (@querystring)

select OBJECT_ID('tempdb..#t') /*NULL, Table Dropped*/

Upvotes: 2

Related Questions