gpt4 book ai didi

sql - 如何使用动态 SQL 生成列组?

转载 作者:行者123 更新时间:2023-12-01 11:51:40 25 4
gpt4 key购买 nike

人们喜欢得到不同种类和数量的水果,我想帮助他们。我有一个看起来像这样的表:

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

每个人可能有 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

来自 PIVOT in sql 2005 的代码当您不知道会有多少行时,可以解析出一列,但我无法让它用于多列。我尝试创建额外的 @select_list 变量,我可以使用它来创建列,但我无法在其中获取我想要的数据,而且我不知道如何像我一样交织顺序想要(例如 f1、c1、t1、f2、c2、t2 与 f1、f2、c1、c2、t1、t2 等)

这是我尝试调整但未成功的代码:

CREATE TABLE #stackoverflowTest(
[name] [varchar](3) NOT NULL,
[fruit] [varchar](12) NOT NULL,
[number] [int] NOT NULL,
[temp] [varchar](4) NOT NULL)

INSERT INTO #stackoverflowTest
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 #stackoverflowTest
) 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 #stackoverflowTest
)
SELECT name, ' + @select_list + '
FROM p
PIVOT (
MIN(fruit)
FOR PIVOT_CODE IN (
' + @pivot_list + '
)
) AS pvt
'
EXEC (@sql)

最佳答案

我真的不会为此尝试使用 PIVOT,它很快就会陷入困惑的境地。

这可能更简单:

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 #stackoverflowTest
) 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 #stackoverflowTest
)
SELECT name, ' + @select_list + '
FROM p GROUP BY name'
EXEC (@sql)

关于sql - 如何使用动态 SQL 生成列组?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/10939026/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com