sqlserver行列转换

一、创建测试数据

--创建测试表

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TestRows2Columns]') AND type in (N'U'))

DROP TABLE [dbo].[TestRows2Columns]

GO

CREATE TABLE [dbo].[TestRows2Columns](

    [Id] [int] IDENTITY(1,1) NOT NULL,

    [UserName] [nvarchar](50) NULL,

    [Subject] [nvarchar](50) NULL,

    [Source] [numeric](18, 0) NULL

) ON [PRIMARY]

GO

　--插入测试数据

INSERT INTO [TestRows2Columns] ([UserName],[Subject],[Source])

    SELECT N'张三',N'语文',60  UNION ALL

    SELECT N'李四',N'数学',70  UNION ALL

    SELECT N'王五',N'英语',80  UNION ALL

    SELECT N'王五',N'数学',75  UNION ALL

    SELECT N'王五',N'语文',57  UNION ALL

    SELECT N'李四',N'语文',80  UNION ALL

    SELECT N'张三',N'英语',100

GO

　　　　 

二、行转列

--行转列

SELECT [UserName],[语文],[数学],[英语]

FROM [TestRows2Columns]

PIVOT

(

    SUM([Source]) for [Subject] in([语文],[数学],[英语])

)TBL

 

三、多行查询合并到一行

--多行转一行

SELECT [UserName],   

       [val]=STUFF( (SELECT ','+[Subject]   

                     FROM [TestRows2Columns] AS secordTable   

                     WHERE secordTable.[UserName] = firstTable.[UserName]   

                     FOR XML PATH('')) , 1 , 1 , '' )  

FROM [TestRows2Columns] AS firstTable    

GROUP BY [UserName] 

　　  

四、列转行测试数据

--列转行

CREATE TABLE Column2Line

(

    [工程名称]         NVARCHAR(20), --工程名称

    海外供应商供给数量        INT,          --海外供应商供给数量

    国内供应商供给数量         INT,          --国内供应商供给数量

    南方供应商供给数量          INT,          --南方供应商供给数量

    北方供应商供给数量          INT           --北方供应商供给数量

)

INSERT INTO Column2Line

SELECT 'A', 100, 200, 50, 50

UNION ALL

SELECT 'B', 200, 300, 150, 150

UNION ALL

SELECT 'C', 159, 400, 20, 320

UNION ALL

SELECT 'D',250,30,15,15

　　 

列转行

SELECT P.[工程名称],P.供应商类型,P.销量
FROM 
(
    SELECT [工程名称], 海外供应商供给数量, 国内供应商供给数量,
           南方供应商供给数量, 北方供应商供给数量
     FROM Column2Line
)T
UNPIVOT 
(
    销量 FOR 供应商类型 IN
    (海外供应商供给数量, 国内供应商供给数量, 南方供应商供给数量, 北方供应商供给数量 )
) P;