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 = '

;开发者_如何学GoWITH 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)

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*/

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