Help creating a Pivot from the follow data

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 2005 Forums

Transact-SQL (2005)

Help creating a Pivot from the follow data

Author

Topic

pizzazzz
Yak Posting Veteran

55 Posts

Posted - 2010-10-21 : 17:02:55

I have been pulling my hair out today trying to create a pivot from the following data, below is the sql script I used; any help would be really appreciated -thx's

EventDate Description Count
9/1/2010 Special Events 11
9/2/2010 Roadshow (Food) 28
9/2/2010 Special Events 24
9/3/2010 Special Events 54
9/3/2010 Roadshow (Food) 42
9/4/2010 Special Events 25
9/4/2010 Roadshow (Food) 22
9/5/2010 Roadshow (Food) 22
9/5/2010 Special Events 27
9/7/2010 Special Events 32
9/8/2010 Special Events 44
9/9/2010 Roadshow (Food) 40
9/9/2010 Special Events 27
9/10/2010 Roadshow (Food) 36
9/10/2010 Special Events 31
9/11/2010 Special Events 19
9/11/2010 Roadshow (Food) 27
9/12/2010 Special Events 53
9/12/2010 Roadshow (Food) 22
9/13/2010 Special Events 2
9/14/2010 Special Events 1
9/15/2010 Special Events 1
9/15/2010 Roadshow (Food) 10

This is the sql script I used -

SELECT
Convert(Varchar(10),tblDemos.DemoDate, 101) as EventTime,
tblServiceType.Description,
Count(tblBreakers.EmployeeInfoID) AS CountOfEmployee

FROM
((((tblDemos

INNER JOIN
tblLocations
ON
tblDemos.LocationsID = tblLocations.LocationsID)

INNER JOIN
tblBillingHeader
ON
tblDemos.DemoID = tblBillingHeader.DemoID)

INNER JOIN
tblBillingDetail
ON
tblBillingHeader.BillingHeaderID = tblBillingDetail.BillingHeaderID)

INNER JOIN
(tblServiceType
INNER JOIN
tblItems
ON
tblServiceType.ServiceTypeID = tblItems.ServiceTypeID)
ON
tblBillingDetail.ItemsID = tblItems.ItemsID)

INNER JOIN
tblBreakers
ON
tblDemos.DemoID = tblBreakers.DemoID

WHERE
(((tblDemos.DemoDate) Between '9/1/2010' And '9/15/2010') AND
((tblDemos.StatusCodeID) In (3,5,6)) AND
((tblLocations.DivisionID)=781) AND
((tblServiceType.ServiceTypeID) In (3,9)))

GROUP BY
tblDemos.DemoDate,
tblServiceType.Description,
tblDemos.StatusCodeID,
tblLocations.DivisionID,
tblServiceType.ServiceTypeID

ORDER BY
tblDemos.DemoDate

sql-programmers
Posting Yak Master

190 Posts

Posted - 2010-10-22 : 05:40:17

You may get better insight through the link

I have a blog article to maybe assist you with your question:

http://www.sql-programmers.com/Blog/tabid/153/EntryId/6/Using-PIVOT-and-UNPIVOT.aspx

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

pizzazzz
Yak Posting Veteran

55 Posts

Posted - 2010-10-22 : 13:13:04

Thanks! I'll take a look at it!

pizzazzz
Yak Posting Veteran

55 Posts

Posted - 2010-10-22 : 13:29:52

Thanks! I've read a lot of articles regarding PIVOT and UNPIVOT, but I still get invalid errors. Any other suggestions?

Sachin.Nand

2937 Posts

Posted - 2010-10-22 : 14:59:59

What is the error & what query are you using?

PBUH

pizzazzz
Yak Posting Veteran

55 Posts

Posted - 2010-10-22 : 15:23:45

The query is in the post above - plus the dataset it produces.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-10-22 : 17:42:21

what is the final result you want to see? it helps for next time if you have something like following


DECLARE @hihi TABLE(EventDate DATETIME, DESCRIPTION NVARCHAR(255), COUNT int)

INSERT INTO  @hihi
        ( EventDate, DESCRIPTION, COUNT )
SELECT '9/1/2010', ' Special Events', 11
UNION ALL
SELECT '9/2/2010', ' Roadshow (Food)', 28
UNION ALL
SELECT '9/2/2010', ' Special Events', 24
UNION ALL
SELECT '9/3/2010', ' Special Events', 54
UNION ALL
SELECT '9/3/2010', ' Roadshow (Food)', 42
UNION ALL
SELECT '9/4/2010', ' Special Events', 25
UNION ALL
SELECT '9/4/2010', ' Roadshow (Food)', 22
UNION ALL
SELECT '9/5/2010', ' Roadshow (Food)', 22
UNION ALL
SELECT '9/5/2010', ' Special Events', 27
UNION ALL
SELECT '9/7/2010', ' Special Events', 32
UNION ALL
SELECT '9/8/2010', ' Special Events', 44
UNION ALL
SELECT '9/9/2010', ' Roadshow (Food)', 40
UNION ALL
SELECT '9/9/2010', ' Special Events', 27
UNION ALL
SELECT '9/10/2010', ' Roadshow (Food)', 36
UNION ALL
SELECT '9/10/2010', ' Special Events', 31
UNION ALL
SELECT '9/11/2010', ' Special Events', 19
UNION ALL
SELECT '9/11/2010', ' Roadshow (Food)', 27
UNION ALL
SELECT '9/12/2010', ' Special Events', 53
UNION ALL
SELECT '9/12/2010', ' Roadshow (Food)', 22
UNION ALL
SELECT '9/13/2010', ' Special Events', 2
UNION ALL
SELECT '9/14/2010', ' Special Events', 1
UNION ALL
SELECT '9/15/2010', ' Special Events', 1
UNION ALL
SELECT '9/15/2010', ' Roadshow (Food)', 10

SELECT * FROM @hihi

If you don't have the passion to help people, you have no passion

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-10-22 : 18:11:13

[code]
create procedure [dbo].[dynamic_pivot]
(
@select varchar(2000),
@PivotCol varchar(100),
@Summaries varchar(100)
) as
declare @pivot varchar(max), @sql varchar(max)
select @select =replace(@select,'select ','select '+@PivotCol+' as pivot_col,')

create table #pivot_columns (pivot_column varchar(100))

Select @sql='select distinct pivot_col from ('+@select+') as t'

insert into #pivot_columns
exec(@sql)

select @pivot=coalesce(@pivot+',','')+'['+pivot_column+']'from #pivot_columns

select @sql=
'
select * from
(
'+@select+'
) as t
pivot
(
'+@Summaries+' for pivot_col in ('+@pivot+')
) as p
'

exec(@sql)

GO

CREATE TABLE hihi (EventDate DATETIME, DESCRIPTION NVARCHAR(255), iCOUNT int)

INSERT INTO hihi
( EventDate, DESCRIPTION, iCOUNT )
SELECT '9/1/2010', ' Special Events', 11
UNION ALL
SELECT '9/2/2010', ' Roadshow (Food)', 28
UNION ALL
SELECT '9/2/2010', ' Special Events', 24
UNION ALL
SELECT '9/3/2010', ' Special Events', 54
UNION ALL
SELECT '9/3/2010', ' Roadshow (Food)', 42
UNION ALL
SELECT '9/4/2010', ' Special Events', 25
UNION ALL
SELECT '9/4/2010', ' Roadshow (Food)', 22
UNION ALL
SELECT '9/5/2010', ' Roadshow (Food)', 22
UNION ALL
SELECT '9/5/2010', ' Special Events', 27
UNION ALL
SELECT '9/7/2010', ' Special Events', 32
UNION ALL
SELECT '9/8/2010', ' Special Events', 44
UNION ALL
SELECT '9/9/2010', ' Roadshow (Food)', 40
UNION ALL
SELECT '9/9/2010', ' Special Events', 27
UNION ALL
SELECT '9/10/2010', ' Roadshow (Food)', 36
UNION ALL
SELECT '9/10/2010', ' Special Events', 31
UNION ALL
SELECT '9/11/2010', ' Special Events', 19
UNION ALL
SELECT '9/11/2010', ' Roadshow (Food)', 27
UNION ALL
SELECT '9/12/2010', ' Special Events', 53
UNION ALL
SELECT '9/12/2010', ' Roadshow (Food)', 22
UNION ALL
SELECT '9/13/2010', ' Special Events', 2
UNION ALL
SELECT '9/14/2010', ' Special Events', 1
UNION ALL
SELECT '9/15/2010', ' Special Events', 1
UNION ALL
SELECT '9/15/2010', ' Roadshow (Food)', 10

EXEC dynamic_pivot 'SELECT EventDate, iCOUNT FROM hihi','DESCRIPTION','SUM (iCOUNT)'

DROP TABLE hihi

[/code]

If you don't have the passion to help people, you have no passion

pizzazzz
Yak Posting Veteran

55 Posts

Posted - 2010-10-22 : 19:33:46

Thanks for the script to create my pivot!!

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-10-23 : 18:36:17

thank Madhivanan

http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

If you don't have the passion to help people, you have no passion

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources