SQL Server 2005 - 在没有总和/计数和动态值列表的情况下透视数据
很抱歉，如果另一个问题的其他地方对此进行了介绍，但我找不到任何与我需要的完全匹配的示例，我感到很困惑.
Sorry if this is covered elsewhere in another question, but I can't find any examples which exactly match what I need and I'm getting confused.
我在表格中有这样的数据:-
I have data in the table like this :-
Name | Value
---------------
John | Dog
John | Cat
John | Fish
Bob | Python
Bob | Camel
我想要这样的数据....
And I would like the data like this....
Name | Value_1 | Value_2 | Value_3
-------------------------------------
John | Dog | Cat | Fish
Bob | Python | Camel | NULL
我不想使用 case 语句，因为狗、猫、鱼等有 20 多个可能的值，而且它们可能会随着时间而改变.
I don't want to use case statements because the dog, cat, fish etc there are 20+ possible values and they could change overtime.
有人指点吗?
推荐答案
这接近于你所追求的.我还包括一个表格创建脚本和示例数据供其他人使用.此解决方案的灵感来自这里
This comes close to what you're after. i've also included a table creation script and sample data for others to use. Inspiration for this solution came from here
-- Dynamic PIVOT
DECLARE @T AS TABLE(y INT NOT NULL PRIMARY KEY);
DECLARE
@cols AS NVARCHAR(MAX),
@y AS INT,
@sql AS NVARCHAR(MAX);
-- Construct the column list for the IN clause
-- e.g., [Dog],[Python]
SET @cols = STUFF(
(SELECT N',' + QUOTENAME(y) AS [text()]
FROM (SELECT DISTINCT [Value] AS y FROM dbo.table_1) AS Y
ORDER BY y
FOR XML PATH('')),
1, 1, N'');
-- Construct the full T-SQL statement
-- and execute dynamically
SET @sql = N'SELECT *
FROM (SELECT *
FROM dbo.table_1) AS D
PIVOT(MIN(value) FOR value IN(' + @cols + N')) AS P;';
PRINT @sql
EXEC sp_executesql @sql;
GO
表创建:
CREATE TABLE [dbo].[Table_1](
[Name] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
[Value] [varchar](50) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO
样本数据:
insert into dbo.table_1 values ('John','Dog')
insert into dbo.table_1 values ('John','Cat')
insert into dbo.table_1 values ('John','Fish')
insert into dbo.table_1 values ('Bob ','Python')
insert into dbo.table_1 values ('Bob ','Camel')
相关文章