MS SQL Server中的Pivot表无上升/整数ID

Question

我在MS-SQL-Server中旋转表的问题。我尝试使用枢轴尝试了不同的事情，但是我没有得到它，仍然有问题以正确。

也许是因为我在表中没有上升的ID号。我已经与SQL一起工作已经有一段时间了，但是我现在真的被困了，希望有人可以帮助我做这件事。

我从此表开始：

id  no1  no2    name    state
-------------------------------
11  5503 NULL   alf     0
11  8533 X1705  ben     0
11  2716 X0608  cha     1
11  5263 X0811  cha     2
11  3158 X0603  cha     2
12  5503 NULL   alf     0
12  8533 X1705  ben     0
12  3158 X0603  cha     2
12  2716 X0608  cha     1
12  5263 X0811  cha     2
13  8731 NULL   alf     0
13  8732 X1705  ben     0
13  4407 X1307  cha     1
14  8247 NULL   alf     0
14  7128 X1705  ben     0
14  5535 X1403  cha     1
14  7281 X9812  cha     0

我想接收一个枢纽的表

id,no1,no2,name,state,no1,no2,name,state,no1,no2,name,state,no1,no2,name,state, …
--------------------------------------------------------------------------------
12,5503,NULL,alf,0,8533,X1705,ben,0,3158,X0603,cha,2,2716,X0608,5263,X0811,cha,2
13,8731,NULL,alf,0,8732,X1705,ben,0,4407,X1307,cha,1
14,8247,NULL,alf,0,7128,X1705,ben,0,5535,X1403,cha,1,7281,X9812,cha,0

每个ID都有＃lways一个" alf"one_answers" ben"的"名称"但是可以最大。带有" cha"的"名称"中的4个条目

基本上，我想基于列" ID"分组"数据集 - 这不是每个数据集的ID。但是我没有int，只有一个唯一的识别符(在此示例中未显示(，也没有看到任何列，我可以在其中使用诸如" sum"或" avg"之类的聚合函数。我可以/我应该使用" max"吗？枢轴甚至可以吗？

Answer 1

我认为在这里使用PIVOT没有逻辑。您似乎只需要通过ID重组数据。我会这样做：

WITH
  s1 AS (SELECT * FROM sample WHERE name = 'alf')
, s2 AS (SELECT * FROM sample WHERE name = 'ben')
, sc AS (SELECT *, rn = row_number() over( partition by id, name order by (SELECT NULL) )
    from sample WHERE name = 'cha')
, s3 AS (SELECT * FROM sc WHERE rn = 1)
, s4 AS (SELECT * FROM sc WHERE rn = 2)
, s5 AS (SELECT * FROM sc WHERE rn = 3)
SELECT s1.* 
    , s2.no1, s2.no2, s2.name, s2.state
    , s3.no1, s3.no2, s3.name, s3.state
    , s4.no1, s4.no2, s4.name, s4.state
    , s5.no1, s5.no2, s5.name, s5.state
    FROM s1
      LEFT JOIN s2 ON s1.id = s2.id
      LEFT JOIN s3 ON s1.id = s3.id
      LEFT JOIN s4 ON s1.id = s4.id
      LEFT JOIN s5 ON s1.id = s5.id
;

Answer 2

您可以使用以下查询；我使用了副画，因为您在ID中只有4个名称。如果有更多您需要实际的字符串分离器功能

这也是现场演示！

select 
id,
no1   =PARSENAME ([1],4),
no2   =PARSENAME ([1],3),
name  =PARSENAME ([1],2),
state =PARSENAME ([1],1),
no1   =PARSENAME ([2],4),
no2   =PARSENAME ([2],3),
name  =PARSENAME ([2],2),
state =PARSENAME ([2],1),
no1   =PARSENAME ([3],4),
no2   =PARSENAME ([3],3),
name  =PARSENAME ([3],2),
state =PARSENAME ([3],1),
no1   =PARSENAME ([4],4),
no2   =PARSENAME ([4],3),
name  =PARSENAME ([4],2),
state =PARSENAME ([4],1)
from
(
    select 
        id, 
        newcol= concat(no1,'.',no2,'.',name,'.',state),
        rn    = row_number() over( partition by id order by (select null))
    from sample
 )src
PIVOT
 ( 
     max(newcol) 
         for rn in 
             ([1],[2],[3],[4])
  )p