I have a table like this in my database (SQL Server 2008)
我的数据库中有这样的表(SQL Server 2008)
ID Type Desc
--------------------------------
C-0 Assets No damage
C-0 Environment No impact
C-0 People No injury or health effect
C-0 Reputation No impact
C-1 Assets Slight damage
C-1 Environment Slight environmental damage
C-1 People First Aid Case (FAC)
C-1 Reputation Slight impact; Compaints from local community
i have to display the Assets, People, Environment and Reputation as columns and display matched Desc as values. But when i run the pivot query, all my values are null.
我必须将Assets,People,Environment和Reputation显示为列,并将匹配的Desc显示为值。但是当我运行数据透视查询时,我的所有值都为空。
Can somebody look into my query ans tell me where i am doing wrong?
有人可以查看我的查询并告诉我我在哪里做错了吗?
Select severity_id,pt.[1] As People, [2] as Assets , [3] as Env, [4] as Rep
FROM
(
select * from COMM.Consequence
) As Temp
PIVOT
(
max([DESCRIPTION])
FOR [TYPE] In([1], [2], [3], [4])
) As pt
Here is my output
这是我的输出
ID People Assets Env Rep
-----------------------------------
C-0 NULL NULL NULL NULL
C-1 NULL NULL NULL NULL
C-2 NULL NULL NULL NULL
C-3 NULL NULL NULL NULL
C-4 NULL NULL NULL NULL
C-5 NULL NULL NULL NULL
2 个解决方案
#1
24
Select severity_id, pt.People, Assets, Environment, Reputation
FROM
(
select * from COMM.Consequence
) As Temp
PIVOT
(
max([DESCRIPTION])
FOR [TYPE] In([People], [Assets], [Environment], [Reputation])
) As pt
#2
0
I recreated this in sql server and it works just fine.
我在sql server中重新创建它,它工作得很好。
I'm trying to convert this to work when one does not know what the content will be in the TYPE and DESCRIPTION columns.
当我不知道TYPE和DESCRIPTION列中的内容是什么时,我正在尝试将其转换为有效。
I was also using this as a guide. (Convert Rows to columns using 'Pivot' in SQL Server)
我也用这个作为指导。 (使用SQL Server中的“Pivot”将行转换为列)
EDIT ----
编辑----
Here is my solution for the above where you DON'T KNOW the content in either field....
这是我上面的解决方案,你不知道任何一个领域的内容....
-- setup commands
drop table #mytemp
go
create table #mytemp (
id varchar(10),
Metal_01 varchar(30),
Metal_02 varchar(100)
)
-- insert the data
insert into #mytemp
select 'C-0','Metal One','Metal_One' union all
select 'C-0','Metal & Two','Metal_Two' union all
select 'C-1','Metal One','Metal_One' union all
select 'C-1','Metal (Four)','Metal_Four' union all
select 'C-2','Metal (Four)','Metal_Four' union all
select 'C-2','Metal / Six','Metal_Six' union all
select 'C-3','Metal Seven','Metal_Seven' union all
select 'C-3','Metal Eight','Metal_Eight'
-- prepare the data for rotating:
drop table #mytemp_ReadyForRotate
select *,
replace(
replace(
replace(
replace(
replace(
mt.Metal_01,space(1),'_'
)
,'(','_'
)
,')','_'
)
,'/','_'
)
,'&','_'
)
as Metal_No_Spaces
into #mytemp_ReadyForRotate
from #mytemp mt
select 'This is the content of "#mytemp_ReadyForRotate"' as mynote, * from #mytemp_ReadyForRotate
-- this is for when you KNOW the content:
-- in this query I am able to put the content that has the punctuation in the cell under the appropriate column header
Select id, pt.Metal_One, Metal_Two, Metal_Four, Metal_Six, Metal_Seven,Metal_Eight
FROM
(
select * from #mytemp
) As Temp
PIVOT
(
max(Metal_01)
FOR Metal_02 In(
Metal_One,
Metal_Two,
Metal_Four,
Metal_Six,
Metal_Seven,
Metal_Eight
)
) As pt
-- this is for when you DON'T KNOW the content:
-- in this query I am UNABLE to put the content that has the punctuation in the cell under the appropriate column header
-- unknown as to why it gives me so much grief - just can't get it to work like the above
-- it WORKS just fine but not with the punctuation field
drop table ##csr_Metals_Rotated
go
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX),
@InsertIntoTempTable as nvarchar(4000)
select @cols = STUFF((SELECT ',' + QUOTENAME(Metal_No_Spaces)
from #mytemp_ReadyForRotate
group by Metal_No_Spaces
order by Metal_No_Spaces
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT id,' + @cols + ' into ##csr_Metals_Rotated from
(
select id as id, Metal_No_Spaces
from #mytemp_ReadyForRotate
) x
pivot
(
max(Metal_No_Spaces)
for Metal_No_Spaces in (' + @cols + ')
) p '
execute(@query);
select * from ##csr_Metals_Rotated
#1
24
Select severity_id, pt.People, Assets, Environment, Reputation
FROM
(
select * from COMM.Consequence
) As Temp
PIVOT
(
max([DESCRIPTION])
FOR [TYPE] In([People], [Assets], [Environment], [Reputation])
) As pt
#2
0
I recreated this in sql server and it works just fine.
我在sql server中重新创建它,它工作得很好。
I'm trying to convert this to work when one does not know what the content will be in the TYPE and DESCRIPTION columns.
当我不知道TYPE和DESCRIPTION列中的内容是什么时,我正在尝试将其转换为有效。
I was also using this as a guide. (Convert Rows to columns using 'Pivot' in SQL Server)
我也用这个作为指导。 (使用SQL Server中的“Pivot”将行转换为列)
EDIT ----
编辑----
Here is my solution for the above where you DON'T KNOW the content in either field....
这是我上面的解决方案,你不知道任何一个领域的内容....
-- setup commands
drop table #mytemp
go
create table #mytemp (
id varchar(10),
Metal_01 varchar(30),
Metal_02 varchar(100)
)
-- insert the data
insert into #mytemp
select 'C-0','Metal One','Metal_One' union all
select 'C-0','Metal & Two','Metal_Two' union all
select 'C-1','Metal One','Metal_One' union all
select 'C-1','Metal (Four)','Metal_Four' union all
select 'C-2','Metal (Four)','Metal_Four' union all
select 'C-2','Metal / Six','Metal_Six' union all
select 'C-3','Metal Seven','Metal_Seven' union all
select 'C-3','Metal Eight','Metal_Eight'
-- prepare the data for rotating:
drop table #mytemp_ReadyForRotate
select *,
replace(
replace(
replace(
replace(
replace(
mt.Metal_01,space(1),'_'
)
,'(','_'
)
,')','_'
)
,'/','_'
)
,'&','_'
)
as Metal_No_Spaces
into #mytemp_ReadyForRotate
from #mytemp mt
select 'This is the content of "#mytemp_ReadyForRotate"' as mynote, * from #mytemp_ReadyForRotate
-- this is for when you KNOW the content:
-- in this query I am able to put the content that has the punctuation in the cell under the appropriate column header
Select id, pt.Metal_One, Metal_Two, Metal_Four, Metal_Six, Metal_Seven,Metal_Eight
FROM
(
select * from #mytemp
) As Temp
PIVOT
(
max(Metal_01)
FOR Metal_02 In(
Metal_One,
Metal_Two,
Metal_Four,
Metal_Six,
Metal_Seven,
Metal_Eight
)
) As pt
-- this is for when you DON'T KNOW the content:
-- in this query I am UNABLE to put the content that has the punctuation in the cell under the appropriate column header
-- unknown as to why it gives me so much grief - just can't get it to work like the above
-- it WORKS just fine but not with the punctuation field
drop table ##csr_Metals_Rotated
go
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX),
@InsertIntoTempTable as nvarchar(4000)
select @cols = STUFF((SELECT ',' + QUOTENAME(Metal_No_Spaces)
from #mytemp_ReadyForRotate
group by Metal_No_Spaces
order by Metal_No_Spaces
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT id,' + @cols + ' into ##csr_Metals_Rotated from
(
select id as id, Metal_No_Spaces
from #mytemp_ReadyForRotate
) x
pivot
(
max(Metal_No_Spaces)
for Metal_No_Spaces in (' + @cols + ')
) p '
execute(@query);
select * from ##csr_Metals_Rotated