SQL中的行的列

问题描述

亲爱的朋友们，我需要你的帮助解决这个问题。

我的数据格式为

名称价值

John 100

John 1000

John 2000

史密斯100

史密斯300



我需要输出

John 100,1000,2000

史密斯100,300



我可以使用这个案例，但现在计数可以是n号。

所以我需要动态解决方案。

请指导。

Dear Friends, I need your help to sort out this issue.
I have a data in the format as
Name Value
John100
John1000
John2000
Smith100
Smith300

I need to output as
John 100, 1000, 2000
Smith 100, 300

I can get this using case when but now the count can be n number.
So i need to dynamic solution.
Please guide.

推荐答案

--Create sample table
create table samplePivot(
Name varchar(50),
itemValue int
)

--Insert sample record
insert into samplePivot(Name,itemValue) values('John',100)
insert into samplePivot(Name,itemValue) values('John',1000)
insert into samplePivot(Name,itemValue) values('John',2000)
insert into samplePivot(Name,itemValue) values('Smith',100)
insert into samplePivot(Name,itemValue) values('Smith',300)

--Pivot query for your result

DECLARE @MyColumns AS NVARCHAR(MAX),
    @SQLquery  AS NVARCHAR(MAX)
-- here first we get all the ItemName which should be display in Columns we use this in our necxt pivot query
select @MyColumns = STUFF((SELECT ',' + QUOTENAME(itemValue) 
                    FROM samplePivot
                    GROUP BY itemValue
                    ORDER BY itemValue
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')
        
        select @MyColumns
        
-- here we use the above all Item name to disoplay its price as column and row display
set @SQLquery = N'SELECT Name,' + @MyColumns + N' from 
             (
                 SELECT 
       Name, 
        itemValue  as itemValue
    FROM samplePivot
            ) x
            pivot 
            (
                 sum(itemValue)
                for itemValue in (' + @MyColumns + N')
            ) p '
exec sp_executesql @SQLquery;

这篇关于SQL中的行的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！