All geek questions in one place

Sort data for PIVOT source

Imagine a table (key value) with attributes and a 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 use the UNPIVOT- operation PIVOTto get data in the form:

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 data independence 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?

Due to the Kind2text in the column Kind_1and Kind3in 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

:

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

KindX Kind_x ..

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

, , PIVOT. ?

: () (SELECT * FROM)

0
1

- , AttributeMask. , Kind, :

select *
from
(
    select LotId,
        SomeText,
        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
        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_Kind1, LotId_Kind1, Value_Kind1, Kind_Kind1,
                attributeid_Kind2, LotId_Kind2, Value_Kind2, Kind_Kind2,
                attributeid_Kind3, LotId_Kind3, Value_Kind3, Kind_Kind3)
) piv;

. SQL Fiddle with Demo. :

| 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 |
0

Source: https://habr.com/ru/post/1544797/

More articles:


All Articles