任何人都可以为此写入查询吗？

Question

我的表如下

recordId fwildcardId refNumber wildcardName wildcardValue comments 
404450 154834    2    aaa    p   p 
404450 154833    1    aa    oi   p 
406115 154867    1    98    ff  ff 
406199 154869    1    aa    aaaa  ssss 
406212 154880    1    bbbbb    card  comm 

，我需要输出作为

 RecordId fwildcardid1 refNo1 Name1 Value1 comments1 fwildcardid2 refNo2 Name2 Value2 comments2 fwildcardid3 refNo3 Name3 Value3 comments3 
     404450 154834   2  aaa  p  p  154833  1  aa oi  p 
     406115 Null   Null Null Null Null  Null  Null Null Null Null 154867  1  98  ff  ff 
     406199 Null   Null Null Null Null 154869  1  aa aaaa  ssss  Null  Null  Null Null 

我试着像下面旋转，但没有成功。

select t1.recordId,t1.wildcardid as fwildcardId,t1.refNo as refNumber,t2.wildcardName,t1.attributeValue as wildcardValue,t1.comments 
into #tempp 
from fwildcards t1 
inner join fwildcardattributes t2 on t2.WildcardID=t1.attributenameid and t2.MarketID=5 
inner join fitems t3 on t3.recordid=t1.recordid and t3.marketid=5 
order by recordid,attributenameid 


select * from #tempp 
pivot (min (wildcardValue) for wildcardName in ([aaa],[aa],[aaaa],[98],[kki],[bbbbb],[SUN])) as wildcardValuePivot 

Answer 1

为了得到这个结果，您将必须UNPIVOT和母鸡的PIVOT数据。 UNPIVOT将取fwildcardId,refNumber,wildcardName,wildcardValue和comments列中的值，并将它们变成行。一旦数据成行，那么您可以应用PIVOT函数来获得最终结果。

要取消转换数据，可以使用UNPIVOT函数，也可以使用CROSS APPLY和VALUES子句。

UNPIVOT：

select recordid, 
    col+cast(rn as varchar(10)) col, 
    unpiv_value 
from 
(
    select recordid, 
    cast(fwildcardid as varchar(10)) fwildcardid, 
    cast(refnumber as varchar(10)) refnumber, 
    cast(wildcardname as varchar(10)) name, 
    cast(wildcardvalue as varchar(10)) value, 
    cast(comments as varchar(10)) comments, 
    row_number() over(partition by recordid 
         order by fwildcardid) rn 
    from tempp 
) d 
unpivot 
(
    unpiv_value 
    for col in (fwildcardid, refnumber, name, value, comments) 
) c 

见SQL Fiddle with Demo。

CROSS APPLY和值：

select recordid, 
    col+cast(rn as varchar(10)) col, 
    value 
from 
(
    select recordid, 
    cast(fwildcardid as varchar(10)) fwildcardid, 
    cast(refnumber as varchar(10)) refnumber, 
    wildcardname, 
    wildcardvalue, 
    comments, 
    row_number() over(partition by recordid 
         order by fwildcardid) rn 
    from tempp 
) d 
cross apply 
(
    values 
    ('fwildcardid', fwildcardid), 
    ('refnumber', refnumber), 
    ('name', wildcardname), 
    ('value', wildcardvalue), 
    ('comments', comments) 
) c (col, value) 

见SQL Fiddle with Demo。

这些转换结果的格式：

| RECORDID |   COL | VALUE | 
------------------------------------ 
| 404450 | fwildcardid1 | 154833 | 
| 404450 | refnumber1 |  1 | 
| 404450 |  name1 |  aa | 
| 404450 |  value1 |  oi | 
| 404450 | comments1 |  p | 
| 404450 | fwildcardid2 | 154834 | 

当你unpivot的数据转换成同一列，它必须是相同的数据类型。您会注意到我对列应用了cast，因此数据类型相同。

一旦数据行格式，您可以将其转换回柱与PIVOT：

select * 
from 
(
    select recordid, 
    col+cast(rn as varchar(10)) col, 
    unpiv_value 
    from 
    (
    select recordid, 
     cast(fwildcardid as varchar(10)) fwildcardid, 
     cast(refnumber as varchar(10)) refnumber, 
     cast(wildcardname as varchar(10)) name, 
     cast(wildcardvalue as varchar(10)) value, 
     cast(comments as varchar(10)) comments, 
     row_number() over(partition by recordid 
         order by fwildcardid) rn 
    from tempp 
) d 
    unpivot 
    (
    unpiv_value 
    for col in (fwildcardid, refnumber, name, value, comments) 
) c 
) src 
pivot 
(
    max(unpiv_value) 
    for col in (fwildcardid1, refnumber1, name1, value1, comments1, 
       fwildcardid2, refnumber2, name2, value2, comments2) 
) piv; 

见SQL Fiddle with Demo。

如果您有已知数量的列，上述版本的工作效果很好，但如果您将有未知数量的值将被转换为列，那么您将需要使用动态sql来获得结果：

DECLARE @cols AS NVARCHAR(MAX), 
    @query AS NVARCHAR(MAX) 

select @cols = STUFF((SELECT ',' + QUOTENAME(c.col+cast(rn as varchar(10))) 
        from 
        (
         select row_number() over(partition by recordid 
               order by fwildcardid) rn 
         from tempp 
        ) t 
        cross apply 
        (
         select 'fwildcardid' col, 1 sortorder union all 
         select 'refNumber', 2 union all 
         select 'name', 3 union all 
         select 'value', 4 union all 
         select 'comments', 5 
        ) c 
        group by col, rn, sortorder 
        order by rn, sortorder 
      FOR XML PATH(''), TYPE 
      ).value('.', 'NVARCHAR(MAX)') 
     ,1,1,'') 

set @query = 'SELECT recordid,' + @cols + ' from 
      (
       select recordid, 
       col+cast(rn as varchar(10)) col, 
       unpiv_value 
       from 
       (
       select recordid, 
        cast(fwildcardid as varchar(10)) fwildcardid, 
        cast(refnumber as varchar(10)) refnumber, 
        cast(wildcardname as varchar(10)) name, 
        cast(wildcardvalue as varchar(10)) value, 
        cast(comments as varchar(10)) comments, 
        row_number() over(partition by recordid 
            order by fwildcardid) rn 
       from tempp 
      ) d 
       unpivot 
       (
       unpiv_value 
       for col in (fwildcardid, refnumber, name, value, comments) 
      ) c 
      ) src 
      pivot 
      (
       max(unpiv_value) 
       for col in (' + @cols + ') 
      ) p ' 

execute(@query); 

见SQL Fiddle with Demo。这两个给出结果：

| RECORDID | FWILDCARDID1 | REFNUMBER1 | NAME1 | VALUE1 | COMMENTS1 | FWILDCARDID2 | REFNUMBER2 | NAME2 | VALUE2 | COMMENTS2 | 
------------------------------------------------------------------------------------------------------------------------------- 
| 404450 |  154833 |   1 | aa |  oi |   p |  154834 |   2 | aaa |  p |   p | 
| 406115 |  154867 |   1 | 98 |  ff |  ff |  (null) |  (null) | (null) | (null) | (null) | 
| 406199 |  154869 |   1 | kki | aaaa |  ssss |  (null) |  (null) | (null) | (null) | (null) | 
| 406212 |  154880 |   1 | bbbbb | card |  comm |  (null) |  (null) | (null) | (null) | (null) | 

Answer 2

无支点无交叉应用

根据编辑问题。

select 
DISTINCT 
A.recordId AS recordId, 
A1.fwildcardId AS fwildcardId1, 
A1.refNumber AS refNumber1, 
A1.wildcardName AS wildcardName1, 
A1.wildcardValue AS wildcardValue1, 
A1.comments AS comments1, 
A2.fwildcardId AS fwildcardId2, 
A2.refNumber AS refNumber2, 
A2.wildcardName AS wildcardName2, 
A2.wildcardValue AS wildcardValue2, 
A2.comments AS comments2, 
A3.fwildcardId AS fwildcardId3, 
A3.refNumber AS refNumber3, 
A3.wildcardName AS wildcardName3, 
A3.wildcardValue AS wildcardValue3, 
A3.comments AS comments3, 
A4.fwildcardId AS fwildcardId4, 
A4.refNumber AS refNumber4, 
A4.wildcardName AS wildcardName4, 
A4.wildcardValue AS wildcardValue4, 
A4.comments AS comments4, 
A5.fwildcardId AS fwildcardId5, 
A5.refNumber AS refNumber5, 
A5.wildcardName AS wildcardName5, 
A5.wildcardValue AS wildcardValue5, 
A5.comments AS comments5, 
A6.fwildcardId AS fwildcardId6, 
A6.refNumber AS refNumber6, 
A6.wildcardName AS wildcardName6, 
A6.wildcardValue AS wildcardValue6, 
A6.comments AS comments6, 
A7.fwildcardId AS fwildcardId7, 
A7.refNumber AS refNumber7, 
A7.wildcardName AS wildcardName7, 
A7.wildcardValue AS wildcardValue7, 
A7.comments AS comments7, 
A8.fwildcardId AS fwildcardId8, 
A8.refNumber AS refNumber8, 
A8.wildcardName AS wildcardName8, 
A8.wildcardValue AS wildcardValue8, 
A8.comments AS comments8, 
A9.fwildcardId AS fwildcardId9, 
A9.refNumber AS refNumber9, 
A9.wildcardName AS wildcardName9, 
A9.wildcardValue AS wildcardValue9, 
A9.comments AS comments9, 
A10.fwildcardId AS fwildcardId10, 
A10.refNumber AS refNumber10, 
A10.wildcardName AS wildcardName10, 
A10.wildcardValue AS wildcardValue10, 
A10.comments AS comments10 
from Table_name A 
LEFt JOIN Table_name A1 ON A.recordId=A1.recordId AND A1.wildcardName='aaa' 
LEFT JOIN Table_name A2 ON A.recordId=A2.recordId AND A2.wildcardName='aa' 
LEFT JOIN Table_name A3 ON A.recordId=A3.recordId AND A3.wildcardName='98' 
LEFT JOIN Table_name A4 ON A.recordId=A4.recordId AND A4.wildcardName='' 
LEFT JOIN Table_name A5 ON A.recordId=A5.recordId AND A5.wildcardName='' 
LEFT JOIN Table_name A6 ON A.recordId=A6.recordId AND A6.wildcardName='' 
LEFT JOIN Table_name A7 ON A.recordId=A7.recordId AND A7.wildcardName='' 
LEFT JOIN Table_name A8 ON A.recordId=A8.recordId AND A8.wildcardName='' 
LEFT JOIN Table_name A9 ON A.recordId=A9.recordId AND A9.wildcardName='' 
LEFT JOIN Table_name A10 ON A.recordId=A10.recordId AND A10.wildcardName='' 

SQL Fiddle