I am using SQL Server 2012, i need help in getting the following output. My table structure is as follows:
我正在使用SQL Server 2012,我需要帮助来获得以下输出。我的表结构如下:
declare @TestTable table (MaterialCode char(5), ItemCode char(5), ItemName varchar(50))
insert into @TestTable values ('AA-01', 'A0001', 'iPhone')
insert into @TestTable values ('AA-02', 'A0001', 'iPad')
insert into @TestTable values ('AA-03', 'A0001', 'iPod')
insert into @TestTable values ('AA-01', 'B0001', 'Galaxy Tab')
insert into @TestTable values ('AA-02', 'B0001', 'Galaxy Note')
insert into @TestTable values ('AA-01', 'C0001', 'Nokia Lumnia')
insert into @TestTable values ('AA-02', 'C0001', 'Motorola')
insert into @TestTable values ('AA-03', 'C0001', 'Samsung S3')
insert into @TestTable values ('AA-04', 'C0001', 'Sony')
--select * from @TestTable
select MaterialCode, ItemCode as [A_ItemCode], ItemName as [A_ItemName]
from @TestTable where ItemCode='A0001'
select MaterialCode, ItemCode as [B_ItemCode], ItemName as [B_ItemName]
from @TestTable where ItemCode='B0001'
select MaterialCode, ItemCode as [C_ItemCode], ItemName as [C_ItemName]
from @TestTable where ItemCode='C0001'
And the output i need should produced from the above three select statements, which should be as follows:
我需要的输出应该来自以上三个select语句,它们应该如下:
As you can see, when there is no record, NULLs are displayed there. Can anyone help me getting this output. TIA.
如您所见,当没有记录时,会显示NULL。任何人都可以帮助我获得此输出。 TIA。
EDIT
@JohnLBevan, I tried your pivot approach and when I have another record which is has ItemCode = D0001
@JohnLBevan,我尝试了你的枢轴方法,当我有另一条记录时,它有ItemCode = D0001
insert into @TestTable values ('AA-05', 'D0001', 'Test1')
now even that record is being displayed as
现在即使该记录显示为
AA-05 NULL NULL NULL NULL NULL NULL
How to avoid these type of records.
如何避免这些类型的记录。
1 个解决方案
#1
3
Not using a pivot, but this gives you the desired results:
不使用枢轴,但这会为您提供所需的结果:
SELECT coalesce(a.MaterialCode, b.MaterialCode, c.MaterialCode) MaterialCode
, a.ItemCode A_ItemCode
, a.ItemName A_ItemName
, b.ItemCode B_ItemCode
, b.ItemName B_ItemName
, c.ItemCode C_ItemCode
, c.ItemName C_ItemName
FROM (select * from @TestTable where ItemCode='A0001') a
full outer join (select * from @TestTable where ItemCode='B0001') b
on a.MaterialCode = b.MaterialCode
full outer join (select * from @TestTable where ItemCode='C0001') c
on a.MaterialCode = c.MaterialCode
or b.MaterialCode = c.MaterialCode
Here's an alternate which uses a pivot, though this feels messier:
这是一个使用枢轴的替代品,虽然这感觉更麻烦:
SELECT MaterialCode
, case when [A0001] is null then null else 'A0001' end A_ItemCode
, [A0001] A_ItemName
, case when [A0001] is null then null else 'B0001' end B_ItemCode
, [B0001] B_ItemName
, case when [A0001] is null then null else 'C0001' end C_ItemCode
, [C0001] C_ItemName
FROM @TestTable a
pivot (
max(ItemName)
for ItemCode in ([A0001],[B0001],[C0001])
) pvt
EDIT
If you want something a little more flexible, here's a dynamic version:
如果你想要一些更灵活的东西,这里是一个动态版本:
declare @dynamicSql nvarchar(max)
, @dynamicSqlPart nvarchar(max)
select * into #TestTable from @TestTable
set @dynamicSql = 'SELECT MaterialCode ' + CHAR(10)
select @dynamicSql = @dynamicSql + ', case when [' + ItemCode + '] is null then null else ''' + ItemCode + ''' end ' + ItemCode + '_ItemCode ' + CHAR(10)
+ ', ' + QUOTENAME(ItemCode) + ' ' + ItemCode + '_ItemName ' + CHAR(10)
, @dynamicSqlPart = ISNULL(@dynamicSqlPart + ',','') + QUOTENAME(ItemCode)
from (select distinct ItemCode from @TestTable) x
set @dynamicSql = @dynamicSql + 'from #TestTable a' + CHAR(10)
+ 'pivot (' + CHAR(10)
+ ' max(ItemName) ' + CHAR(10)
+ ' for ItemCode in (' + @dynamicSqlPart + ')' + CHAR(10)
+ ') pvt' + CHAR(10)
print isnull(@dynamicSql,'?')
exec (@dynamicSql)
drop table #TestTable
#1
3
Not using a pivot, but this gives you the desired results:
不使用枢轴,但这会为您提供所需的结果:
SELECT coalesce(a.MaterialCode, b.MaterialCode, c.MaterialCode) MaterialCode
, a.ItemCode A_ItemCode
, a.ItemName A_ItemName
, b.ItemCode B_ItemCode
, b.ItemName B_ItemName
, c.ItemCode C_ItemCode
, c.ItemName C_ItemName
FROM (select * from @TestTable where ItemCode='A0001') a
full outer join (select * from @TestTable where ItemCode='B0001') b
on a.MaterialCode = b.MaterialCode
full outer join (select * from @TestTable where ItemCode='C0001') c
on a.MaterialCode = c.MaterialCode
or b.MaterialCode = c.MaterialCode
Here's an alternate which uses a pivot, though this feels messier:
这是一个使用枢轴的替代品,虽然这感觉更麻烦:
SELECT MaterialCode
, case when [A0001] is null then null else 'A0001' end A_ItemCode
, [A0001] A_ItemName
, case when [A0001] is null then null else 'B0001' end B_ItemCode
, [B0001] B_ItemName
, case when [A0001] is null then null else 'C0001' end C_ItemCode
, [C0001] C_ItemName
FROM @TestTable a
pivot (
max(ItemName)
for ItemCode in ([A0001],[B0001],[C0001])
) pvt
EDIT
If you want something a little more flexible, here's a dynamic version:
如果你想要一些更灵活的东西,这里是一个动态版本:
declare @dynamicSql nvarchar(max)
, @dynamicSqlPart nvarchar(max)
select * into #TestTable from @TestTable
set @dynamicSql = 'SELECT MaterialCode ' + CHAR(10)
select @dynamicSql = @dynamicSql + ', case when [' + ItemCode + '] is null then null else ''' + ItemCode + ''' end ' + ItemCode + '_ItemCode ' + CHAR(10)
+ ', ' + QUOTENAME(ItemCode) + ' ' + ItemCode + '_ItemName ' + CHAR(10)
, @dynamicSqlPart = ISNULL(@dynamicSqlPart + ',','') + QUOTENAME(ItemCode)
from (select distinct ItemCode from @TestTable) x
set @dynamicSql = @dynamicSql + 'from #TestTable a' + CHAR(10)
+ 'pivot (' + CHAR(10)
+ ' max(ItemName) ' + CHAR(10)
+ ' for ItemCode in (' + @dynamicSqlPart + ')' + CHAR(10)
+ ') pvt' + CHAR(10)
print isnull(@dynamicSql,'?')
exec (@dynamicSql)
drop table #TestTable