Query help, rows to columns - 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

General SQL Server Forums

New to SQL Server Programming

Query help, rows to columns

Author

Topic

ddt
Starting Member

10 Posts

Posted - 2010-04-27 : 20:26:16

Hi from begginer
I am not sure what is the right way to ask this question so i wasn't getting any google hits.

I have two tables (e.g. customers and venues).
Customers
custid customername
----- ------------
1001 jones
1002 smith
...

Venues
custid venueid
------ ---------
1001 9001
1001 9008
1001 9009
1002 9002
1002 9008
1002 9010
...

I need to create a view and return values in a table that will tell me if a venue was used by all customers we have
result needs to look like this
custid 9002 9008
------ ---- ----
1001 Y N
1002 Y Y
....
Any help is appreciated...
SQL 2005 SP3
I need to create a view

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-04-27 : 21:20:54

are you using SQL Server 2000 or 2005/2008 ?

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-04-27 : 21:37:47

[code]
declare @Customers table
(
custid int,
customername varchar(5)
)

insert into @Customers
select 1001, 'jones' union all
select 1002, 'smith'

declare @Venues table
(
custid int,
venueid int
)
insert into @Venues
select 1001, 9001 union all
select 1001, 9008 union all
select 1001, 9009 union all
select 1002, 9002 union all
select 1002, 9008 union all
select 1002, 9010

select custid,
[9001] = isnull([9001], 'N'),
[9002] = isnull([9002], 'N'),
[9008] = isnull([9008], 'N'),
[9010] = isnull([9010], 'N')
from (
select v.custid,
venueid,
vflag = case when count(*) over (partition by venueid) = ccnt
then 'Y'
else 'N'
end
from @Venues v
cross apply
(
select ccnt = count(*)
from @Customers
) c
) v
pivot
(
max(vflag)
for venueid in ([9001], [9002], [9008], [9010])
) p

/*
custid 9001 9002 9008 9010
----------- ---- ---- ---- ----
1001 N N Y N
1002 N N Y N

(2 row(s) affected)
*/[/code]

KH
[spoiler]Time is always against us[/spoiler]

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-04-28 : 04:37:55

[code]CREATE VIEW view_1 AS
SELECT C.custid,
MAX(CASE WHEN venueid = 9002 THEN 'Y' ELSE 'N' END) AS [9002],
MAX(CASE WHEN venueid = 9008 THEN 'Y' ELSE 'N' END) AS [9008]
FROM Customers C
LEFT OUTER JOIN Venues V
ON C.custid = V.custid
GROUP BY C.custid[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-28 : 05:42:02

in case you want the pivot values to be determined dynamically,use

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

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ddt
Starting Member

10 Posts

Posted - 2010-04-28 : 21:35:11

thanks for helping out. i will try it out and let you know if i was successful

ddt
Starting Member

10 Posts

Posted - 2010-04-29 : 20:22:13

I started with the query from ms65g as it looked simpler and it worked. i am looking at khtan's query now as i have little experience with programmable objects...

thanks for your help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources