Pivot - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Pivot

Author

Topic

chriztoph
Posting Yak Master

184 Posts

Posted - 2013-08-29 : 01:24:38

Hi Guys,

I need help here. I have a pivot query that create dynamic columns for me but there is problem on it.

In my where clause there is the between [from date - to date] search. My code's result only shows:


COMPANYID	May 24 2013
1	        1

what I want is:


COMPANYID | May 20 2013 | May 21 2013 | May 22 2013 | May 23 2013 | May 24 2013 | May 25 2013
1	    0             0             0             0             1             0

Sample Data:


COMPANYID   |	BLNUMBER     |	RECEIVEDATE
1	        5050084819	2013-05-24
1	        5480407740	2013-05-17
1	        5480407741	2013-05-17
1	        5480407742	2013-05-17
1	        5480407743	2013-05-17
1	        5480407744	2013-05-17
1	        5480407670	2013-05-17
1	        5480407671	2013-05-17
1	        5581367430	2013-05-17
1	        558136743	2013-05-17

Here is my code:

DECLARE @colsPivot AS NVARCHAR(MAX),
    @colsUnpivot as NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @colsPivot = STUFF((SELECT distinct ',' + QUOTENAME(RECEIVEDATE) 
                    from dbo.OSUSR_G8V_BLINFO WHERE RECEIVEDATE >= '2013-05-20' AND RECEIVEDATE <= '2013-05-25'
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

select @colsUnpivot = stuff((select ','+quotename(C.name)
         from sys.columns as C
         where C.object_id = object_id('OSUSR_G8V_BLINFO') and
               C.name LIKE 'BLNUMBER%'
         for xml path('')), 1, 1, '')

set @query 
  = 'select *
      from
      (
        select COMPANYID, RECEIVEDATE, VAL
        from dbo.OSUSR_G8V_BLINFO
        unpivot
        (
          VAL
          for col in ('+ @colsunpivot +')
        ) u
      ) x1
      pivot
      (
        count(VAL)
        for RECEIVEDATE in ('+ @colspivot +')
      ) p'

exec(@query)

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-08-29 : 02:13:24

[code]DECLARE @colsPivot AS NVARCHAR(MAX) = '',
@colsUnpivot as NVARCHAR(MAX),
@query AS NVARCHAR(MAX),
@startDate DATE ='2013-05-20', @EndDate DATE = '2013-05-25'

--select @colsPivot = STUFF((SELECT distinct ',' + QUOTENAME(RECEIVEDATE)
-- from dbo.OSUSR_G8V_BLINFO WHERE RECEIVEDATE >= '2013-05-20' AND RECEIVEDATE <= '2013-05-25'
-- FOR XML PATH(''), TYPE
-- ).value('.', 'NVARCHAR(MAX)')
-- ,1,1,'')

;WITH Dates(DateRange)
AS (SELECT @StartDate
UNION ALL
SELECT DATEADD( DD, 1, DateRange)
FROM Dates
WHERE DATEADD( DD, 1, DateRange) <= @EndDate
)
SELECT @colsPivot = ',[' + CAST( DateRange AS VARCHAR(10))+ ']'+ @colsPivot
FROM Dates

SET @colsPivot = STUFF(@colsPivot, 1,1,'')

select @colsUnpivot = stuff((select ','+quotename(C.name)
from sys.columns as C
where C.object_id = object_id('OSUSR_G8V_BLINFO') and
C.name LIKE 'BLNUMBER%'
for xml path('')), 1, 1, '')

set @query
= 'select *
from
(
select COMPANYID, RECEIVEDATE, VAL
from dbo.OSUSR_G8V_BLINFO
unpivot
(
VAL
for col in ('+ @colsunpivot +')
) u
) x1
pivot
(
count(VAL)
for RECEIVEDATE in ('+ @colspivot +')
) p'

exec(@query)[/code]

--
Chandu

chriztoph
Posting Yak Master

184 Posts

Posted - 2013-08-29 : 02:28:25

No result for column 2013-05-24


COMPANYID	2013-05-20	2013-05-21	2013-05-22	2013-05-23	2013-05-24	2013-05-25
1	        0	        0	        0	        0	        0	        0

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-08-29 : 02:43:52

-- Look into the highlighted part

;WITH Dates(DateRange) 
AS (SELECT @StartDate
	UNION ALL
	SELECT DATEADD( DD, 1, DateRange)
	FROM Dates 
	WHERE DATEADD( DD, 1, DateRange) <= @EndDate
	)
SELECT @colsPivot = ',[' + REPLACE( CONVERT(VARCHAR(12), DateRange , 107), ',', '') + ']'+  @colsPivot
FROM Dates

--
Chandu

chriztoph
Posting Yak Master

184 Posts

Posted - 2013-08-29 : 02:50:31

still no result.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-08-29 : 02:52:51

Why.. ? Have you run the script together...?
Check the below script

-- Sample Data
CREATE TABLE OSUSR_G8V_BLINFO(COMPANYID   int, BLNUMBER     bigint, RECEIVEDATE DATE)
insert OSUSR_G8V_BLINFO
SELECT 1, 5050084819,	'2013-05-24' union all
SELECT 1, 5480407740,	'2013-05-17' union all
SELECT 1,   5480407741,	'2013-05-17' union all
SELECT 1,   5480407742,	'2013-05-17' union all
SELECT 1,    5480407743,	'2013-05-17' union all
SELECT 1,    5480407744,	'2013-05-17' union all
SELECT 1,    5480407670,	'2013-05-17' union all
SELECT 1,    5480407671,	'2013-05-17' union all
SELECT 1,    5581367430,	'2013-05-17' union all
SELECT 1,    558136743,	'2013-05-17'

-- Query Script
DECLARE @colsPivot AS NVARCHAR(MAX) = '',
    @colsUnpivot as NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX),
    @startDate DATE ='2013-05-20', @EndDate DATE = '2013-05-25'

;WITH Dates(DateRange) 
AS (SELECT @StartDate  --SELECT CONVERT(VARCHAR(12), SYSDATETIME(), 107) AS [Mon DD, YYYY]
	UNION ALL
	SELECT DATEADD( DD, 1, DateRange)
	FROM Dates 
	WHERE DATEADD( DD, 1, DateRange) <= @EndDate
	)
SELECT @colsPivot = ',[' + REPLACE( CONVERT(VARCHAR(12), DateRange , 107), ',', '') + ']'+  @colsPivot
FROM Dates

SET @colsPivot = STUFF(@colsPivot, 1,1,'')
SELECT @colsPivot
select @colsUnpivot = stuff((select ','+quotename(C.name)
         from sys.columns as C
         where C.object_id = object_id('OSUSR_G8V_BLINFO') and
               C.name LIKE 'BLNUMBER%'
         for xml path('')), 1, 1, '')

set @query 
  = 'select *
      from
      (
        select COMPANYID, RECEIVEDATE, VAL
        from dbo.OSUSR_G8V_BLINFO
        unpivot
        (
          VAL
          for col in ('+ @colsunpivot +')
        ) u
      ) x1
      pivot
      (
        count(VAL)
        for RECEIVEDATE in ('+ @colspivot +')
      ) p'

exec(@query)
/*OUTPUT:
COMPANYID	May 25 2013	May 24 2013	May 23 2013	May 22 2013	May 21 2013	May 20 2013
1	0	1	0	0	0	0*/