sql语句使列中的行 [英] sql statement to make row in columns

问题描述

我有一个包含以下数据的表:

I have a table with the following data:

FIELD1            FIELD2   FIELD3
01/04/2012        A        100
01/04/2012        B        150
02/04/2012        A        100
02/04/2012        B        125
03/04/2012        A        100
04/04/2012        A        130     
04/04/2012        B        160
05/05/2012        B        138
06/05/2012        A        150
06/05/2012        B        160

现在，我想编写一条SQL语句，其中数据应该在像
这样的列中可见

Now I want to write a SQL statement where data should visible in column like

FIELD1            FIELD2   FIELD3    FIELD4       FIELD5
01/04/2012        A        100       B            150
02/04/2012        A        100       B            125    
03/04/2012        A        100
04/04/2012        A        130       B            160
05/05/2012                           B            138
06/05/2012        A        150       B            160

在此先感谢您！

Thanks in advance!

推荐答案

您需要使用PIVOT

SQL-具有总计列和行总数的数据透视 [ ^ ]

You need to use PIVOT

SQL - Pivot with Grand Total Column and Row[^]

正如Mark Mischalke所说:您需要使用数据透视.

示例:

As Mark Mischalke wrote: you need to use a pivot.

Example:

IF NOT  OBJECT_ID(N'#Table1',N'U') IS NULL 
	DROP TABLE #Table1
--create temporary table
CREATE TABLE #Table1 ([FIELD1] DATETIME, [FIELD2] VARCHAR(5), [FIELD3] INT)
--insert data
INSERT INTO #Table1 ([FIELD1] , [FIELD2] , FIELD3 )
	VALUES('01/04/2012','A', 100)
INSERT INTO #Table1 ([FIELD1] , [FIELD2] , FIELD3 )
	VALUES('01/04/2012','B', 150)
INSERT INTO #Table1 ([FIELD1] , [FIELD2] , FIELD3 )
	VALUES('02/04/2012','A', 100)
INSERT INTO #Table1 ([FIELD1] , [FIELD2] , FIELD3 )
	VALUES('02/04/2012','B', 125)
INSERT INTO #Table1 ([FIELD1] , [FIELD2] , FIELD3 )
	VALUES('03/04/2012','A', 100)
INSERT INTO #Table1 ([FIELD1] , [FIELD2] , FIELD3 )
	VALUES('04/04/2012','A', 130)
INSERT INTO #Table1 ([FIELD1] , [FIELD2] , FIELD3 )
	VALUES('04/04/2012','B', 160)
INSERT INTO #Table1 ([FIELD1] , [FIELD2] , FIELD3 )
	VALUES('05/05/2012','B', 138)
INSERT INTO #Table1 ([FIELD1] , [FIELD2] , FIELD3 )
	VALUES('06/05/2012','A', 150)
INSERT INTO #Table1 ([FIELD1] , [FIELD2] , FIELD3 )
	VALUES('06/05/2012','B', 160)
--declare variables
DECLARE @cols1 VARCHAR(200)
DECLARE @dt1 VARCHAR(2000)
DECLARE @pt1 VARCHAR(4000)
--get columns - dynamicly!
SET @cols1 = STUFF((SELECT DISTINCT '],[' + [FIELD2]
					FROM #Table1
					ORDER BY '],[' + [FIELD2]
			FOR XML PATH('')),1,2,'') + ']'
--SELECT @cols1

--get all data 
SET @dt1 = 'SELECT * ' + 
		'FROM #Table1'
--EXEC(@dt1)

--pivot data by A, B, C, ...
SET @pt1 = 'SELECT CONVERT(VARCHAR,PT.[FIELD1],110) AS [FIELD1], ' + @cols1 + ' ' +
		'FROM (' + @dt1 + ') AS DT ' + 
		'PIVOT(MAX([FIELD3]) FOR [FIELD2] IN (' + @cols1 + ')) AS PT' 

EXEC (@pt1)

--delete temporary table
DROP TABLE #Table1

结果:

Result:

[FIELD1]	[A]	[B]
01-04-2012	100	150
02-04-2012	100	125
03-04-2012	100	NULL
04-04-2012	130	160
05-05-2012	NULL	138
06-05-2012	150	160

SELECT DISTINCT rd.Field1,
    (SELECT rd2.FIELD2 FROM RawData rd2 WHERE rd2.Field1 = rd.Field1 and rd2.Field2 = 'A') as Field2, 
    (SELECT rd2.FIELD3 FROM RawData rd3 WHERE rd3.Field1 = rd.Field1 and rd3.Field2 = 'A') as Field3,
    (SELECT rd4.FIELD2 FROM RawData rd4 WHERE rd4.Field1 = rd.Field1 and rd4.Field2 = 'B') as Field4,
    (SELECT rd5.FIELD3 FROM RawData rd5 WHERE rd5.Field1 = rd.Field1 and rd5.Field2 = 'B') as Field5
FROM RawData rd

仅当每个日期表中最多有两个记录时，此方法才有效.

问候，

曼弗雷德(Manfred)

This will only work if there are at most two records in your table for each date.

Regards,

Manfred

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