在SQL中不透明？请帮忙 [英] Unpivot in SQL ? please help

问题描述

你好，



我有以下查询工作正常，但如果我要添加其他列来解开，我收到错误。





源表数据：



ID StateID SourceID Department EffectiveDate

8 36 1全部2015-08-01

65 36 2全部2015-08-01



预期输出：



FieldName FieldValue

ID 8

StateID 36

SourceID 1

部门全部

EffectiveDate 2015-08-01

ID 65

StateID 36

部门全部

EffectiveDate 2015-08-01

Hello,

I have the following query works fine, but if I were to additional columns to unpivot,I am getting the error.


Source Table Data:

ID StateID SourceID Department EffectiveDate
8 36 1 All 2015-08-01
65 36 2 All 2015-08-01

Expected Output:

FieldName FieldValue
ID 8
StateID 36
SourceID 1
Department All
EffectiveDate 2015-08-01
ID 65
StateID 36
Department All
EffectiveDate 2015-08-01

推荐答案

declare @tab table(
	ID int, StateID int, SourceID int, Department varchar(max), EffectiveDate date
)	
insert into @tab values
(8,	36,	1, 'All', '2015-08-01'),
(65, 36, 2, 'All', '2015-08-01')

SELECT FieldName, FieldValue
FROM   (
    SELECT	cast(ID as varchar) as ID 
			, cast(StateID as varchar) as StateID 
			, cast(SourceID as varchar) as SourceID
			, cast(Department as varchar) as Department
			, cast(EffectiveDate as varchar) as EffectiveDate
    FROM	@tab
) AS t1  
UNPIVOT (
    FieldValue FOR FieldName IN (ID, StateID, SourceID, Department, EffectiveDate)
) AS t2

请参考以下链接，你我会对如何实施它有一个很好的想法。



http://blog.sqlauthority.com/2008/05/29/sql-server-unpivot-table-example/ [ ^ ]

Please refer the below link, you will get a fair idea on how to implement it.

http://blog.sqlauthority.com/2008/05/29/sql-server-unpivot-table-example/[^]

这篇关于在SQL中不透明？请帮忙的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！