SqlServer 行转列问题

怀念十月 2019-03-05 08:50:43

已有数据

如何才能将数据显示为下面这样

用行转列出来的结果

	SELECT  * FROM #TempTbl AS p PIVOT (

		max(Result) FOR p.DateId IN ([20160125],[20160225])

	) AS T

有没有谁有奇淫巧技?

...全文

217 5 打赏收藏转发到动态举报

写回复

用AI写文章

5 条回复

切换为时间正序

请发表友善的回复…

发表回复

吉普赛的歌 2019-03-06

打赏
举报

USE tempdb
GO
IF OBJECT_ID('dbo.[t]') IS NOT NULL 
	DROP TABLE dbo.[t]
GO
CREATE TABLE dbo.[t](
[Name] NVARCHAR(20)
,[DateId] INT
,Result	INT
)
GO
SET NOCOUNT ON
INSERT INTO dbo.[t] VALUES(N'15优先A1',20160125,1)
INSERT INTO dbo.[t] VALUES(N'15优先A1',20160225,2)
INSERT INTO dbo.[t] VALUES(N'15优先A2',20160125,1)
INSERT INTO dbo.[t] VALUES(N'15优先A2',20160225,2)
INSERT INTO dbo.[t] VALUES(N'15优先A3',20160125,1)
INSERT INTO dbo.[t] VALUES(N'15优先A3',20160225,2)
INSERT INTO dbo.[t] VALUES(N'15优先B',20160125,1)
INSERT INTO dbo.[t] VALUES(N'15优先B',20160225,2)
INSERT INTO dbo.[t] VALUES(N'15优先C',20160125,1)
INSERT INTO dbo.[t] VALUES(N'15优先C',20160225,2)

--静态写法 （仅能用于Name 固定的情况）
SELECT DateId
,SUM(CASE WHEN Name='15优先A1' THEN Result ELSE 0 END) AS [15优先A1]
,SUM(CASE WHEN Name='15优先A2' THEN Result ELSE 0 END) AS [15优先A2]
,SUM(CASE WHEN Name='15优先A3' THEN Result ELSE 0 END) AS [15优先A3]
,SUM(CASE WHEN Name='15优先B' THEN Result ELSE 0 END) AS [15优先B]
,SUM(CASE WHEN Name='15优先C' THEN Result ELSE 0 END) AS [15优先B]
FROM t
GROUP BY [DateId]

--动态写法 
DECLARE @sql NVARCHAR(MAX)
SET  @sql='SELECT DateId'
+(SELECT ',SUM(CASE WHEN Name='''+NAME+''' THEN Result ELSE 0 END) AS ['+t.Name+']' FROM t GROUP BY NAME FOR XML PATH(''))
+' FROM t
GROUP BY [DateId]
'
PRINT @sql
EXEC(@sql)

墨月羿 2019-03-06

打赏
举报

学习了，之前只知道一条条写出来

怀念十月 2019-03-06

打赏
举报

引用 2 楼吉普赛的歌的回复:

USE tempdb
GO
IF OBJECT_ID('dbo.[t]') IS NOT NULL 
	DROP TABLE dbo.[t]
GO
CREATE TABLE dbo.[t](
[Name] NVARCHAR(20)
,[DateId] INT
,Result	INT
)
GO
SET NOCOUNT ON
INSERT INTO dbo.[t] VALUES(N'15优先A1',20160125,1)
INSERT INTO dbo.[t] VALUES(N'15优先A1',20160225,2)
INSERT INTO dbo.[t] VALUES(N'15优先A2',20160125,1)
INSERT INTO dbo.[t] VALUES(N'15优先A2',20160225,2)
INSERT INTO dbo.[t] VALUES(N'15优先A3',20160125,1)
INSERT INTO dbo.[t] VALUES(N'15优先A3',20160225,2)
INSERT INTO dbo.[t] VALUES(N'15优先B',20160125,1)
INSERT INTO dbo.[t] VALUES(N'15优先B',20160225,2)
INSERT INTO dbo.[t] VALUES(N'15优先C',20160125,1)
INSERT INTO dbo.[t] VALUES(N'15优先C',20160225,2)

--静态写法 （仅能用于Name 固定的情况）
SELECT DateId
,SUM(CASE WHEN Name='15优先A1' THEN Result ELSE 0 END) AS [15优先A1]
,SUM(CASE WHEN Name='15优先A2' THEN Result ELSE 0 END) AS [15优先A2]
,SUM(CASE WHEN Name='15优先A3' THEN Result ELSE 0 END) AS [15优先A3]
,SUM(CASE WHEN Name='15优先B' THEN Result ELSE 0 END) AS [15优先B]
,SUM(CASE WHEN Name='15优先C' THEN Result ELSE 0 END) AS [15优先B]
FROM t
GROUP BY [DateId]

--动态写法 
DECLARE @sql NVARCHAR(MAX)
SET  @sql='SELECT DateId'
+(SELECT ',SUM(CASE WHEN Name='''+NAME+''' THEN Result ELSE 0 END) AS ['+t.Name+']' FROM t GROUP BY NAME FOR XML PATH(''))
+' FROM t
GROUP BY [DateId]
'
PRINT @sql
EXEC(@sql)

引用 3 楼二月十六的回复:

借2#版主数据

USE tempdb
GO
IF OBJECT_ID('dbo.[t]') IS NOT NULL 
    DROP TABLE dbo.[t]
GO
CREATE TABLE dbo.[t](
[Name] NVARCHAR(20)
,[DateId] INT
,Result    INT
)
GO
SET NOCOUNT ON
INSERT INTO dbo.[t] VALUES(N'15优先A1',20160125,1)
INSERT INTO dbo.[t] VALUES(N'15优先A1',20160225,2)
INSERT INTO dbo.[t] VALUES(N'15优先A2',20160125,1)
INSERT INTO dbo.[t] VALUES(N'15优先A2',20160225,2)
INSERT INTO dbo.[t] VALUES(N'15优先A3',20160125,1)
INSERT INTO dbo.[t] VALUES(N'15优先A3',20160225,2)
INSERT INTO dbo.[t] VALUES(N'15优先B',20160125,1)
INSERT INTO dbo.[t] VALUES(N'15优先B',20160225,2)
INSERT INTO dbo.[t] VALUES(N'15优先C',20160125,1)
INSERT INTO dbo.[t] VALUES(N'15优先C',20160225,2)


DECLARE @sql VARCHAR(MAX)
SET @sql = 'select DateId'
SELECT  @sql = @sql + ',max(case Name when ''' + RTRIM(Name) 
        + ''' then Result else 0 end)[' + RTRIM(Name) + ']'
FROM    ( SELECT DISTINCT
                    [Name]
          FROM      T
        ) a
SET @sql = @sql
    + ' from T group by DateId'

EXEC(@sql)

感谢两位大佬的慷慨.思维很清晰. 结帖啦..

二月十六 2019-03-06

打赏
举报

借2#版主数据

USE tempdb

GO

IF OBJECT_ID('dbo.[t]') IS NOT NULL 

    DROP TABLE dbo.[t]

GO

CREATE TABLE dbo.[t](

[Name] NVARCHAR(20)

,[DateId] INT

,Result    INT

)

GO

SET NOCOUNT ON

INSERT INTO dbo.[t] VALUES(N'15优先A1',20160125,1)

INSERT INTO dbo.[t] VALUES(N'15优先A1',20160225,2)

INSERT INTO dbo.[t] VALUES(N'15优先A2',20160125,1)

INSERT INTO dbo.[t] VALUES(N'15优先A2',20160225,2)

INSERT INTO dbo.[t] VALUES(N'15优先A3',20160125,1)

INSERT INTO dbo.[t] VALUES(N'15优先A3',20160225,2)

INSERT INTO dbo.[t] VALUES(N'15优先B',20160125,1)

INSERT INTO dbo.[t] VALUES(N'15优先B',20160225,2)

INSERT INTO dbo.[t] VALUES(N'15优先C',20160125,1)

INSERT INTO dbo.[t] VALUES(N'15优先C',20160225,2)





DECLARE @sql VARCHAR(MAX)

SET @sql = 'select DateId'

SELECT  @sql = @sql + ',max(case Name when ''' + RTRIM(Name) 

        + ''' then Result else 0 end)[' + RTRIM(Name) + ']'

FROM    ( SELECT DISTINCT

                    [Name]

          FROM      T

        ) a

SET @sql = @sql

    + ' from T group by DateId'



EXEC(@sql)