Another SQL Pivot question - 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)

Another SQL Pivot question

Author

Topic

Blade Hunter
Starting Member

6 Posts

Posted - 2012-03-21 : 21:31:03

Hi Guys, thanks so much for the help yesterday, I feel I am finally starting to understand these pivot tables.

I need to group some data and that part I am lost on.

So I have a pivot returning:

Date Of Birth in column 1
Redemtion, download, acc created....... in the following columns (all from the pivot)

The Date of Birth column which is the none pivoted column needs to have brackets and respective summing.

What I mean by this is instead of returning every unique DOB, I need to bracket them into the following:

<18 years old
18-20
21-25
26-30
and so on until I get to the end which is 76+.

Here is my existing WORKING query minus this bracketing.


SELECT	D_DOB,
		[310] 'STD VCH REDEMP',
		[510] 'STD DOWNLOAD',
		[410] 'STD STR ACC CREATED',
   		[110] 'STD REG',
		[120] 'FB REG',
		[121] 'TWT REG',
		[210] 'STD LOGIN',
		[220] 'FB LOGIN',
		[221] 'TWT LOGIN'
FROM
		(
			SELECT	(UA.N_ACTIVITY_TYPE*10)+UA.N_SOURCE_TYPE N_ACTIVITY_TYPE,
					U.D_DOB,
					1 as TransCount
			FROM BanditWarehouseV3.dbo.DSP_USER_ACTIVITY_LOG UA, BanditWarehouseV3.dbo.DSP_USER U
			WHERE convert(DATE,dateadd(HH,-17,UA.D_CREATED),112) BETWEEN '2012-03-01' AND '2012-03-31'
			AND UA.N_USER_ID = U.N_USER_ID
		) AS SourceTable
PIVOT(
		count(TransCount)
		FOR N_ACTIVITY_TYPE IN (
			[310],
			[510],
			[410],
			[110],
			[120],
			[121],
			[210],
			[220],
			[221]
		)
) AS PivotTable
order by D_DOB desc;

Once again thanks in advance for any help offered, I really appreciate it.

Cheers

Dan

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-03-21 : 22:10:08

change the inner query (SourceTable) to group the DOB


SELECT	N_ACTIVITY_TYPE, AGE_GROUP, TransCount = sum(TransCount)
FROM
(
	SELECT	(UA.N_ACTIVITY_TYPE*10)+UA.N_SOURCE_TYPE N_ACTIVITY_TYPE,
		U.D_DOB,
	         AGE_GROUP = case when datediff(year, U.D_DOB, getdate() < 18 then '< 18'
                          when datediff(year, U.D_DOB, getdate() between 19 and 20 then '18-20'
                          . . .
                          end,
		1 as TransCount
	FROM 	BanditWarehouseV3.dbo.DSP_USER_ACTIVITY_LOG UA, BanditWarehouseV3.dbo.DSP_USER U
	WHERE 	convert(DATE,dateadd(HH,-17,UA.D_CREATED),112) BETWEEN '2012-03-01' AND '2012-03-31'
	AND 	UA.N_USER_ID = U.N_USER_ID
) d
GROUP BY N_ACTIVITY_TYPE, AGE_GROUP