sub-query in pivot - 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)

sub-query in pivot

Author

Topic

jung1975
Aged Yak Warrior

503 Posts

Posted - 2008-01-01 : 11:40:12

Select
consume_ID,

(select cities from reasion a join contry b on a.region_id = b.region_id where region_cd = 99 )

FROM
(SELECT

SELECT Consumer_ID , Visit_value,city_test

FROM person p INNER JOIN Inventory I on p.inventory_id = I.inventory_id)

WHERE (Visits_ID = 4)

) p
PIVOT
(
count(visit_Value) For city IN
( select cities from reasion a join contry b on a.region_id = b.region_id where region_cd = 99 )

) pvt;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-01 : 11:57:09

PIVOT wont work like this. You cant specify a query for giving the values to be pivoted on. Please refer to PIVOT syntax on BOL.

b/w Can i ask you what you are trying to achieve here? You may be able to achieve this even without using PIVOT.

jung1975
Aged Yak Warrior

503 Posts

Posted - 2008-01-01 : 18:42:48

customer_id city visit_value
1 Tokyo 2
1 Seoul 1
1 Newyork 2
2 Chicago 1

I would like to make the cities to columns .. like below

customerId Tokyo seoul newyork chicago
1 2 1 2 0
2 0 0 0 1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-02 : 00:11:25

either do like this if city names are certain:-

CREATE TABLE #t
(
 customer_id  int,
city varchar(20),
visit_value int
)


INSERT INTO #t VALUES (1,'Tokyo',2)
INSERT INTO #t VALUES (1, 'Seoul', 1)
INSERT INTO #t VALUES (1, 'Newyork', 2) 
INSERT INTO #t VALUES (2, 'Chicago', 1)


SELECT 
customer_id,
SUM(CASE WHEN city='Newyork' THEN visit_value ELSE 0 END) AS 'Newyork',
SUM(CASE WHEN city='Seoul' THEN visit_value ELSE 0 END) AS 'Seoul',
SUM(CASE WHEN city='Chicago' THEN visit_value ELSE 0 END) AS 'Chicago',
SUM(CASE WHEN city='Tokyo' THEN visit_value ELSE 0 END) AS 'Tokyo'

FROM #t 
GROUP BY customer_id

else apply PIVOT after builting the string of city names and buidling a sql string as

@Sql='Select
consumer_ID,

(select cities from reasion a join contry b on a.region_id = b.region_id where region_cd = 99 )

FROM
(SELECT

SELECT Consumer_ID , Visit_value,city_test

FROM person p INNER JOIN Inventory I on p.inventory_id = I.inventory_id)

WHERE (Visits_ID = 4)

) p
PIVOT
(
count(visit_Value) For city IN
('+ @CityList + ')

) pvt'

Where @CityList='Tokyo','Seoul',....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources