People like to get different types and amounts of fruit and I want to help them. I have a table that looks like this:
人们喜欢吃不同种类和数量的水果,我想帮助他们。我有一张这样的桌子:
name fruit count temp
-----------------------------
Jim apple 3 hot
Jim banana 7 cold
Jim orange 12 cold
Sam plum 5 hot
Sam peach 1 hot
Bob cherry 4 cold
Bob banana 11 hot
Bob orange 9 cold
Bob kiwi 6 hot
Each person could have 1 or 1000 rows and I will not know how many ahead of time so I think this requires dynamic column lists. I need it to look like this:
每个人可能有1或1000行,我不知道提前多少行,所以我认为这需要动态列列表。我需要它看起来像这样:
name fruit_1 count_1 temp_1 fruit_2 count_2 temp_2 fruit_3 count_3 temp_3 fruit_4 count_4 temp_4
-------------------------------------------------------------------------------------------------------
Jim apple 3 hot banana 7 cold orange 12 cold null null null
Sam plum 5 hot peach 1 hot null null null null null null
Bob cherry 4 cold banana 11 hot orange 9 cold kiwi 6 hot
The code from PIVOT in sql 2005 works for parsing out one column when you don't know how many rows there will be, but I can't get it to work for multiple columns. I tried creating extra @select_list
variables, and I can use that to create the columns, but I can't get the data I want in them and I don't know how to interweave the order like I want (eg f1,c1,t1,f2,c2,t2 vs f1,f2,c1,c2,t1,t2 etc)
当您不知道会有多少行时,从sql 2005中的PIVOT代码可以解析出一个列,但是我不能让它为多个列工作。我尝试创建额外的@select_list变量,我可以使用它来创建列,但是我不能获得我想要的数据,我不知道如何像我想要的那样交织顺序(例如f1,c1,t1,f2,c2,t2 vs f1,f2,c1,c2, c2, c2,t1,t2等等)
Here is the code I have been unsuccessfully trying to tweak:
下面是我一直试图修改的代码:
CREATE TABLE #*Test(
[name] [varchar](3) NOT NULL,
[fruit] [varchar](12) NOT NULL,
[number] [int] NOT NULL,
[temp] [varchar](4) NOT NULL)
INSERT INTO #*Test
VALUES ('Jim','apple',3,'hot'),
('Jim','banana',7,'cold'),
('Jim','orange',12,'cold'),
('Sam','plum',5,'hot'),
('Sam','peach',1,'hot'),
('Bob','cherry',4,'cold'),
('Bob','banana',11,'hot'),
('Bob','orange',9,'cold'),
('Bob','kiwi',6,'hot')
DECLARE @sql AS varchar(max)
DECLARE @pivot_list AS varchar(max)
DECLARE @select_list AS varchar(max)
SELECT @pivot_list = COALESCE(@pivot_list + ', ', '') + '[' + CONVERT(varchar, PIVOT_CODE) + ']'
,@select_list = COALESCE(@select_list + ', ', '') + '[' + CONVERT(varchar, PIVOT_CODE) + '] AS [fruit_' + CONVERT(varchar, PIVOT_CODE) + ']'
FROM (
SELECT DISTINCT PIVOT_CODE
FROM (
SELECT name, fruit, number, temp, ROW_NUMBER() OVER (PARTITION BY name ORDER BY name,number) AS PIVOT_CODE
FROM #*Test
) AS rows
) AS PIVOT_CODES
SET @sql = '
;WITH p AS (
SELECT name, fruit, number, temp, ROW_NUMBER() OVER (PARTITION BY name ORDER BY name,number) AS PIVOT_CODE
FROM #*Test
)
SELECT name, ' + @select_list + '
FROM p
PIVOT (
MIN(fruit)
FOR PIVOT_CODE IN (
' + @pivot_list + '
)
) AS pvt
'
EXEC (@sql)
1 个解决方案
#1
5
I wouldn't really try using PIVOT for that, it gets into a headmess situation pretty quickly.
我不会尝试使用主元,它很快就会陷入混乱。
This is probably simpler:
这可能是简单的:
DECLARE @sql AS varchar(max)
DECLARE @select_list AS varchar(MAX)
SELECT @select_list = ISNULL(@select_list + ', ', '') + 'SUM(CASE WHEN PIVOT_CODE = ' + [PIVOT_CODE] + ' THEN number ELSE NULL END) AS number_' + [PIVOT_CODE] + ', MIN(CASE WHEN PIVOT_CODE = ' + [PIVOT_CODE] + ' THEN temp ELSE NULL END) AS temp_' + [PIVOT_CODE] + ', MIN(CASE WHEN PIVOT_CODE = ' + [PIVOT_CODE] + ' THEN fruit ELSE NULL END) AS fruit_' + [PIVOT_CODE]
FROM (
SELECT DISTINCT PIVOT_CODE
FROM (
SELECT name, fruit, number, temp, CONVERT(varchar(20), ROW_NUMBER() OVER (PARTITION BY name ORDER BY NAME,number)) AS PIVOT_CODE
FROM #*Test
) AS rows
) AS PIVOT_CODES
SET @sql = '
;WITH p AS (
SELECT name, fruit, number, temp, ROW_NUMBER() OVER (PARTITION BY name ORDER BY name,number) AS PIVOT_CODE
FROM #*Test
)
SELECT name, ' + @select_list + '
FROM p GROUP BY name'
EXEC (@sql)
#1
5
I wouldn't really try using PIVOT for that, it gets into a headmess situation pretty quickly.
我不会尝试使用主元,它很快就会陷入混乱。
This is probably simpler:
这可能是简单的:
DECLARE @sql AS varchar(max)
DECLARE @select_list AS varchar(MAX)
SELECT @select_list = ISNULL(@select_list + ', ', '') + 'SUM(CASE WHEN PIVOT_CODE = ' + [PIVOT_CODE] + ' THEN number ELSE NULL END) AS number_' + [PIVOT_CODE] + ', MIN(CASE WHEN PIVOT_CODE = ' + [PIVOT_CODE] + ' THEN temp ELSE NULL END) AS temp_' + [PIVOT_CODE] + ', MIN(CASE WHEN PIVOT_CODE = ' + [PIVOT_CODE] + ' THEN fruit ELSE NULL END) AS fruit_' + [PIVOT_CODE]
FROM (
SELECT DISTINCT PIVOT_CODE
FROM (
SELECT name, fruit, number, temp, CONVERT(varchar(20), ROW_NUMBER() OVER (PARTITION BY name ORDER BY NAME,number)) AS PIVOT_CODE
FROM #*Test
) AS rows
) AS PIVOT_CODES
SET @sql = '
;WITH p AS (
SELECT name, fruit, number, temp, ROW_NUMBER() OVER (PARTITION BY name ORDER BY name,number) AS PIVOT_CODE
FROM #*Test
)
SELECT name, ' + @select_list + '
FROM p GROUP BY name'
EXEC (@sql)