Transforming To Bits - 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 2005 Forums

Transact-SQL (2005)

Transforming To Bits

Author

Topic

jmpierce30
Starting Member

7 Posts

Posted - 2009-12-16 : 16:51:02

I have a question

I have a table that contains promotions that someone received
ID Promo
-- -----
1 a
1 c
1 d
2 b
2 c
2 d

and i would like an output table to come as

ID a b c d
-- - - - -
1 1 0 1 1
2 0 1 1 1

any help would be greatly appreciated i'd like to find a very efficient way to do this without writing a ton of update statements to do so

while (I != Understand)
{
KickMe.Hard;
}

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-12-16 : 17:05:05

have you looked into the PIVOT command

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

jmpierce30
Starting Member

7 Posts

Posted - 2009-12-16 : 17:09:43

I know that it exists, but I have not used it before.

Could anyone provide a simple example that works with this dataset so that I can learn?

Thanks in advance for your help :-)

while (I != Understand)
{
KickMe.Hard;
}

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-12-16 : 17:31:54

This should be a start..

declare @t table (ID int, promo varchar(1))
insert @t
select 1, 'a'
union all select 1,'c'
union all select 1, 'd'
union all select 2, 'b'
union all select 2, 'c'
union all select 2, 'd'

select ID,[a],[b],[c],[d] from
(
select ID,promo from @t
) p
PIVOT
(
COUNT(promo) FOR promo IN ([a],[b],[c],[d])
) as pvt

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-12-16 : 18:05:46

vijayisonly is on fire today folks, I don't know what he ate or what he drank..I will have what he is having or whatever he had.

and if you want to get fancy you can use one of those dynamic PIVOT functions like so (this is helpful for when the values of proo are dynamic)

exec usp_pivot 'dbo', 'PivotTest', 'ID', 'promo','COUNT', 'promo'

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-17 : 02:17:24

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

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources