Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Pivoting?

Status
Not open for further replies.
wbodger

wbodger

Programmer
Apr 23, 2007
769
US
OK, I have this query

Code: 
SELECT	 cast(DATEADD(dd, -(DATEPART(dw, STUDY_CONSENT_DATE)-1), STUDY_CONSENT_DATE)as DATE) as [WeekStart],
		cast(DATEADD(dd, 7-(DATEPART(dw, STUDY_CONSENT_DATE)), STUDY_CONSENT_DATE) as DATE) as [WeekEnd],
		datepart(ww,STUDY_CONSENT_DATE) as weeknum,
		STUDY_SITE_ID,
		COUNT(datepart(ww,STUDY_CONSENT_DATE)) as countforsite
  FROM [db].[dbo].[TBLSTUDYDOC]
  where STUDY_PROTOCOL_VISIT_CODE=1
  and DELETEFLAG=0 and ENTRYFLAG=1
  group by datepart(ww,STUDY_CONSENT_DATE), study_site_id,DATEADD(dd, -(DATEPART(dw, STUDY_CONSENT_DATE)-1), STUDY_CONSENT_DATE),
		DATEADD(dd, 7-(DATEPART(dw, STUDY_CONSENT_DATE)), STUDY_CONSENT_DATE)
  order by DATEADD(dd, -(DATEPART(dw, STUDY_CONSENT_DATE)-1), STUDY_CONSENT_DATE)

which gives me

Code: 
WeekStart	WeekEnd	weeknum	STUDY_SITE_ID	countforsite
2011-09-25	2011-10-01	40	661	1
2011-10-02	2011-10-08	41	661	2
2011-10-09	2011-10-15	42	661	2
2011-10-09	2011-10-15	42	677	2
2011-10-16	2011-10-22	43	661	5
2011-10-16	2011-10-22	43	676	2
2011-10-16	2011-10-22	43	677	4
2011-10-23	2011-10-29	44	661	2
2011-10-23	2011-10-29	44	676	1
2011-10-23	2011-10-29	44	677	1
2011-10-30	2011-11-05	45	661	4
2011-10-30	2011-11-05	45	676	2
2011-10-30	2011-11-05	45	677	3
2011-11-06	2011-11-12	46	661	6
2011-11-06	2011-11-12	46	676	1
2011-11-06	2011-11-12	46	677	2
2011-11-13	2011-11-19	47	661	8

However, what I would like it to look like is weekstart, weekend, weeknum as the 'row names', study_site_id as the 'column names' and then countforsite as the values.

Code: 
weekstart       weekend        weeknum  661	676	677
9/25/2011	10/1/2011	40	1		
10/2/2011	10/8/2011	41	2		
10/9/2011	10/15/2011	42	2		2
10/16/2011	10/22/2011	43	5	2	4
10/23/2011	10/29/2011	44	2	1	1
10/30/2011	11/5/2011	45	4	2	3
11/6/2011	11/12/2011	46	6	1	2
11/13/2011	11/19/2011	47	8

And I don't get how to use PIVOT to get this to work. It seems like ti should be fairly simple, but I don't see it. So, any pointers out there?

wb
 
Sort by date Sort by votes
This is a simplified sample that I cannot get to work

Code: 
SELECT * FROM
	(
	    SELECT	 cast(DATEADD(dd, -(DATEPART(dw, STUDY_CONSENT_DATE)-1), STUDY_CONSENT_DATE)as DATE) as [WeekStart],
		cast(DATEADD(dd, 7-(DATEPART(dw, STUDY_CONSENT_DATE)), STUDY_CONSENT_DATE) as DATE) as [WeekEnd],
		datepart(ww,STUDY_CONSENT_DATE) as weeknum,
		STUDY_SITE_ID,
		count(datepart(ww,STUDY_CONSENT_DATE)) as countforsite
  FROM [dbEDRN320].[dbo].[TBLSTUDYDOC]
  where STUDY_PROTOCOL_VISIT_CODE=1
  and DELETEFLAG=0 and ENTRYFLAG=1
  group by datepart(ww,STUDY_CONSENT_DATE), study_site_id,DATEADD(dd, -(DATEPART(dw, STUDY_CONSENT_DATE)-1), STUDY_CONSENT_DATE),
		DATEADD(dd, 7-(DATEPART(dw, STUDY_CONSENT_DATE)), STUDY_CONSENT_DATE)
	)t
	PIVOT 
	(count(datepart(ww,STUDY_CONSENT_DATE)) FOR study_site_id IN (661,67)
) as pvt

with the error
Code: 
Msg 102, Level 15, State 1, Line 15
Incorrect syntax near '('.

line 15 is

Code: 
(count(datepart(ww,STUDY_CONSENT_DATE)) FOR study_site_id IN (661,67)

Any thoughts?

wb
 
Upvote 0 Downvote
Count your parenthesis in that line. You have 4 open "(" parenthesis and only 3 close ")" parenthesis.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875
 
Upvote 0 Downvote
Whoops...my bad....after I sent the response I saw the last close parenthesis on the next line.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875
 
Upvote 0 Downvote
Actually, I believe I figured it out. I have only been able to test in our dev environment because production is set to compatibility level 80, but this seems to work

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

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(study_site_id) 
                    from TBLSTUDYDOC
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')
        
        	 
	SET @query =
	'SELECT * FROM
	(
	    SELECT	 DATEADD(dd, -(DATEPART(dw, STUDY_CONSENT_DATE)-1), STUDY_CONSENT_DATE)  [WeekStart],
		DATEADD(dd, 7-(DATEPART(dw, STUDY_CONSENT_DATE)), STUDY_CONSENT_DATE)  [WeekEnd],
		datepart(ww,STUDY_CONSENT_DATE)  weeknum,
		STUDY_SITE_ID
  FROM [db].[dbo].[TBLSTUDYDOC]
  where STUDY_PROTOCOL_VISIT_CODE=1
  and DELETEFLAG=0 and ENTRYFLAG=1
  group by datepart(ww,STUDY_CONSENT_DATE), study_site_id,DATEADD(dd, -(DATEPART(dw, STUDY_CONSENT_DATE)-1), STUDY_CONSENT_DATE),
		DATEADD(dd, 7-(DATEPART(dw, STUDY_CONSENT_DATE)), STUDY_CONSENT_DATE)
	)t
	PIVOT (count(study_site_id) FOR study_site_id
	IN ('+@cols+') ) pvt'
	 
	EXECUTE (@query)

wb
 
Upvote 0 Downvote
I was wrong, something is not quite right. When I run the inner SQL

Code: 
SELECT cast(DATEADD(dd, -(DATEPART(dw, STUDY_CONSENT_DATE)-1), STUDY_CONSENT_DATE) as DATE)  [WeekStart],
cast(DATEADD(dd, 7-(DATEPART(dw, STUDY_CONSENT_DATE)), STUDY_CONSENT_DATE) as DATE)  [WeekEnd],
row_number() over(order by DATEADD(dd, -(DATEPART(dw, STUDY_CONSENT_DATE)-1), STUDY_CONSENT_DATE))  weeknum,
STUDY_SITE_ID,
count(study_site_id) as totals
FROM [dbEDRN320].[dbo].[TBLSTUDYDOC]
where STUDY_PROTOCOL_VISIT_CODE=1
and DELETEFLAG=0 and ENTRYFLAG=1
group by datepart(ww,STUDY_CONSENT_DATE), study_site_id,DATEADD(dd, -(DATEPART(dw, STUDY_CONSENT_DATE)-1), STUDY_CONSENT_DATE),DATEADD(dd, 7-(DATEPART(dw, STUDY_CONSENT_DATE)), STUDY_CONSENT_DATE)

i get

Code: 
weekstart       weekend        weeknum  study_site_id  totals
2011-01-30	2011-02-05	1	661              1
2011-12-11	2011-12-17	2	67               2

But when I run the entire thing

Code: 
SELECT * FROM
	(
	    SELECT cast(DATEADD(dd, -(DATEPART(dw, STUDY_CONSENT_DATE)-1), STUDY_CONSENT_DATE) as DATE)  [WeekStart],
		cast(DATEADD(dd, 7-(DATEPART(dw, STUDY_CONSENT_DATE)), STUDY_CONSENT_DATE) as DATE)  [WeekEnd],
		row_number() over(order by DATEADD(dd, -(DATEPART(dw, STUDY_CONSENT_DATE)-1), STUDY_CONSENT_DATE))  weeknum,
		STUDY_SITE_ID,
                count(study_site_id) as totals
  FROM [dbEDRN320].[dbo].[TBLSTUDYDOC]
  where STUDY_PROTOCOL_VISIT_CODE=1
  and DELETEFLAG=0 and ENTRYFLAG=1
  group by datepart(ww,STUDY_CONSENT_DATE), study_site_id,DATEADD(dd, -(DATEPART(dw, STUDY_CONSENT_DATE)-1), STUDY_CONSENT_DATE),
		DATEADD(dd, 7-(DATEPART(dw, STUDY_CONSENT_DATE)), STUDY_CONSENT_DATE)
	)t
	PIVOT (count(study_site_id) FOR study_site_id IN ([661],[67]) 
) as pvt
order by weeknum asc

I get

Code: 
weekstart       weekend       weeknum  totals  661     67
2011-01-30	2011-02-05	1	1	1	0
2011-12-11	2011-12-17	2	2	0	1

The inner query is returning the correct data, a total of 2 for site 67 in weeknum 2, but the full query is only returning 1 for site 67 in weeknum 2. Any thoughts?

Willie
 
Upvote 0 Downvote
For some reason, my row_number() was screwing up my overall results, so I removed that and the GROUP BY and everything seems to be fine.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

waubain
  • Locked
  • Question
Data not pivoting as expected 1
Replies
4
Views
28
Olaf Doschke
Olaf Doschke
sds814
  • Locked
  • Question
Pivoting rows to columns 1
Replies
5
Views
48
gmmastros
gmmastros
rivi10
  • Locked
  • Question
Pivoting without NULL 2
Replies
7
Views
45
gmmastros
gmmastros
mkal
  • Locked
  • Question
Pivoting data
Replies
1
Views
16
Welshbird
Welshbird
brucegonewild
  • Locked
  • Question
dynamic pivoting
Replies
3
Views
33
brucegonewild
brucegonewild

Part and Inventory Search

Sponsor

Back
Top