Imagine a table (key-value) with the Attributes and the parent table with Lots.
LotId SomeText
----------- --------
1 Hello
2 World
AttributeId LotId Val Kind
----------- ----------- -------- --------
1 1 Foo1 Kind1
2 1 Foo2 Kind2
3 2 Bar1 Kind1
4 2 Bar2 Kind2
5 2 Bar3 Kind3
I am using UNPIVOT - PIVOT operation to get the data in the form of:
LotId SomeText AttributeId LotId Kind1Val Kind AttributeId LotId Kind2Val Kind AttributeId LotId Kind3Val Kind
----------- -------- ----------- ----------- -------- -------- ----------- ----------- -------- -------- ----------- ----------- -------- --------
1 Hello 1 1 Foo1 Kind1 2 1 Foo2 Kind2 NULL NULL NULL NULL
2 World 3 2 Bar1 Kind1 4 2 Bar2 Kind2 5 2 Bar3 Kind3
How to choose the data independence away from the value of the attribute table.
An example of an incorrect result:
LotId SomeText attributeid_1 LotId_1 Value_1 Kind_1 attributeid_2 LotId_2 Value_2 Kind_2 attributeid_3 LotId_3 Value_3 Kind_3
----------- -------- ------------- -------- -------- -------- ------------- -------- -------- -------- ------------- -------- -------- --------
1 Hello 4 1 Foo1 Kind1 NULL NULL NULL NULL NULL NULL NULL NULL
2 World 1 2 Bar2 Kind2 3 2 Bar3 Kind3 2 2 Bar1 Kind8
Why?
Because of Kind2 text in Kind_1 column and Kind3 in Kind_2.
SQL Fiddle
DECLARE @Lot TABLE (
LotId INT PRIMARY KEY IDENTITY,
SomeText VARCHAR(8))
INSERT INTO @Lot
VALUES ('Hello'), ('World')
DECLARE @Attribute TABLE(
AttributeId INT PRIMARY KEY IDENTITY,
LotId INT,
Val VARCHAR(8),
Kind VARCHAR(8))
INSERT INTO @Attribute VALUES
(2, 'Bar2', 'Kind2'),
(2, 'Bar1', 'Kind8'),
(2, 'Bar3', 'Kind3'),
(1, 'Foo1', 'Kind1')
select *
from
(
select LotId,
SomeText,
col+'_'+CAST(rn as varchar(10)) col,
value
from
(
select l.LotId,
l.SomeText,
cast(a.AttributeId as varchar(8)) attributeid,
cast(a.LotId as varchar(8)) a_LotId,
a.Val,
a.Kind,
ROW_NUMBER() over(partition by l.lotid order by a.kind) rn
from @Lot l
left join @Attribute a
on l.LotId = a.LotId
) src
cross apply
(
values ('attributeid', attributeid),('LotId', a_LotId), ('Value', Val), ('Kind', Kind)
) c (col, value)
) d
pivot
(
max(value)
for col in (attributeid_1, LotId_1, Value_1, Kind_1,
attributeid_2, LotId_2, Value_2, Kind_2,
attributeid_3, LotId_3, Value_3, Kind_3)
) piv
An example of the correct result:
LotId SomeText attributeid_Kind1 LotId_Kind1 Value_Kind1 Kind_Kind1 attributeid_Kind2 LotId_Kind2 Value_Kind2 Kind_Kind2 attributeid_Kind3 LotId_Kind3 Value_Kind3 Kind_Kind3
----------- -------- ----------------- ----------- ----------- ---------- ----------------- ----------- ----------- ---------- ----------------- ----------- ----------- ----------
1 WithAll 1 1 Foo1 Kind1 2 1 Foo2 Kind2 3 1 Foo3 Kind3
2 Hello NULL NULL NULL NULL 10 2 Bar2 Kind2 NULL NULL NULL NULL
3 World NULL NULL NULL NULL NULL NULL NULL NULL 12 3 Bar3 Kind3
Because KindX is in Kind_x column, etc.
SQL Fiddle
DECLARE @Lot TABLE (
LotId INT PRIMARY KEY IDENTITY,
SomeText VARCHAR(8))
INSERT INTO @Lot
VALUES ('WithAll'), ('Hello'), ('World')
DECLARE @Attribute TABLE(
AttributeId INT PRIMARY KEY IDENTITY,
LotId INT,
Val VARCHAR(8),
Kind VARCHAR(8))
INSERT INTO @Attribute VALUES
(1, 'Foo1', 'Kind1'),
(1, 'Foo2', 'Kind2'),
(1, 'Foo3', 'Kind3'),
(1, 'Foo4', 'Kind4'),
(1, 'Foo5', 'Kind5'),
(1, 'Foo6', 'Kind6'),
(1, 'Foo7', 'Kind7'),
(1, 'Foo8', 'Kind8'),
(1, 'Foo9', 'Kind9'),
(2, 'Bar2', 'Kind2'),
(2, 'Bar1', 'Kind8'),
(3, 'Bar3', 'Kind3')
DECLARE @AttributesMask TABLE(
Kind VARCHAR(8)
)
INSERT INTO @AttributesMask
VALUES('Kind1'), ('Kind2'), ('Kind3'), ('Kind4'), ('Kind5'), ('Kind6'), ('Kind7'), ('Kind8')
select * from(
select LotId,
SomeText,
--col+'_'+CAST(rn as varchar(10)) col,
col+'_'+[Kind] col,
value
from
(
select l.LotId,
l.SomeText,
cast(a.AttributeId as varchar(8)) attributeid,
cast(a.LotId as varchar(8)) a_LotId,
a.Val,
a.Kind
--, ROW_NUMBER() over(partition by l.[LotId] order by am.[Kind]) rn
FROM @AttributesMask AS am
LEFT join @Attribute a on [am].[Kind] = [a].[Kind]
LEFT JOIN @Lot l ON [a].[LotId] = [l].[LotId]
) src
cross apply
(
values ('attributeid', attributeid),('LotId', a_LotId), ('Value', Val), ('Kind', Kind)
) c (col, value)
) d PIVOT (max(value) for col in (
attributeid_Kind1, LotId_Kind1, Value_Kind1, Kind_Kind1,
attributeid_Kind2, LotId_Kind2, Value_Kind2, Kind_Kind2,
attributeid_Kind3, LotId_Kind3, Value_Kind3, Kind_Kind3)) piv
ORDER BY LotId
To get the correct result I used the mask to pre-arrange the data that are the source of PIVOT. How to do it without the mask?
Referring to the question: How to replace a functional (many) OUTER APPLY (SELECT * FROM)
