.NET中的PIVOT表 [英] PIVOT Table in .NET

问题描述

HI，



我正在使用StoredProcedure在myu Grid中显示结果...



我的结果集如下所示

HI ,

I'm using StoredProcedure to display result in myu Grid...

My result set is look like this

Doc_Id	CL_Id	CL_Desc	       Rev_No	dwg_status_code	EMP_Name
146508	15	C-BF-FT-02 R2	0	DWS000002	NULL
146508	15	C-BF-FT-02 R2	0	DWS000003	NULL
146508	15	C-BF-FT-02 R2	0	DWS000025	NULL
146508	16	C-BF-FT-03 R2	0	DWS000002	KARTHIKEYAN K
146508	16	C-BF-FT-03 R2	0	DWS000003	KARTHIKEYAN K
146508	16	C-BF-FT-03 R2	0	DWS000025	KARTHIKEYAN K

但是我想在我的网格中自动显示这样的

But i want to display in my grid it self like this

Doc_Id	CL_Id	CL_Desc	       Rev_No	DWS000002    DWS000003      DWS000025	
146508	15	C-BF-FT-02 R2	0	NULL	     NULL           NULL
146508	16	C-BF-FT-03 R2	0	KARTHIKEYAN   KARTHIKEYAN    KARTHIKEYAN 

喜欢SQL中的PIVOT，但在Coding方面如何做这个逻辑。任何人都可以给我一些关于此的建议...

like PIVOT in SQL , But in Coding side how to do this logic. Can anyone give me some suggestion regarding this...

推荐答案

请参阅以下内容：

C＃Pivot Table [ ^ ]



http://stackoverflow.com/questions/963491/pivot-data-using-linq [ ^ ]

See the following :
C# Pivot Table[^]

http://stackoverflow.com/questions/963491/pivot-data-using-linq[^]

试试这个：

Try this:

IF NOT OBJECT_ID(N'#tmp') IS NULL DROP TABLE #tmp


CREATE TABLE #tmp(Doc_Id INT, CL_Id INT, CL_Desc NVARCHAR(30), Rev_No INT, dwg_status_code NVARCHAR(30), EMP_Name NVARCHAR(30))

INSERT INTO #tmp (Doc_Id, CL_Id, CL_Desc, Rev_No, dwg_status_code, EMP_Name)
VALUES(146508, 15, 'C-BF-FT-02 R2',	0,	'DWS000002', NULL)
INSERT INTO #tmp (Doc_Id, CL_Id, CL_Desc, Rev_No, dwg_status_code, EMP_Name)
VALUES(146508, 15, 'C-BF-FT-02 R2',	0,	'DWS000003', NULL)
INSERT INTO #tmp (Doc_Id, CL_Id, CL_Desc, Rev_No, dwg_status_code, EMP_Name)
VALUES(146508, 15, 'C-BF-FT-02 R2',	0,	'DWS000025', NULL)
INSERT INTO #tmp (Doc_Id, CL_Id, CL_Desc, Rev_No, dwg_status_code, EMP_Name)
VALUES(146508, 16, 'C-BF-FT-03 R2',	0,	'DWS000002', 'KARTHIKEYAN K')
INSERT INTO #tmp (Doc_Id, CL_Id, CL_Desc, Rev_No, dwg_status_code, EMP_Name)
VALUES(146508, 16, 'C-BF-FT-03 R2',	0,	'DWS000003', 'KARTHIKEYAN K')
INSERT INTO #tmp (Doc_Id, CL_Id, CL_Desc, Rev_No, dwg_status_code, EMP_Name)
VALUES(146508, 16, 'C-BF-FT-03 R2',	0,	'DWS000025', 'KARTHIKEYAN K')

--SELECT *
--FROM #tmp

DECLARE @cols NVARCHAR(300)
DECLARE @dt NVARCHAR(2000)
DECLARE @pt NVARCHAR(MAX)

SET @cols = STUFF((SELECT DISTINCT '],[' + dwg_status_code
					FROM #tmp
					ORDER BY '],[' + dwg_status_code
			FOR XML PATH('')),1,2,'') + ']'

SET @dt = N'SELECT * FROM #tmp'

SET @pt = N'SELECT Doc_Id, CL_Id, CL_Desc, Rev_No, ' + @cols + ' ' +
		'FROM ( ' + @dt + ') AS DT ' + 
		'PIVOT(MIN([EMP_Name]) FOR [dwg_status_code] IN(' + @cols + ')) AS PT'

EXEC(@pt)

DROP TABLE #tmp

这篇关于.NET中的PIVOT表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！