SQL Server-将一列的值显示为具有新列的单行

正如我在评论中所说,目前,就目前的情况来看,由于缺乏身份、订购和标签,您将获得的订单完全是随机的。因此,您可以使用此选项,但每次运行查询时,结果都会更改:

USE Sandbox;
GO

CREATE TABLE dbo.SampleData(Code varchar(15),
                            Reason varchar(4),
                            testValues varchar(7));
GO
INSERT INTO dbo.SampleData (Code,
                            Reason,
                            testValues)
VALUES ('Phlebotomist','test','12345'),
       ('Phlebotomist','test','23456'),
       ('Phlebotomist','test',''),
       ('Phlebotomist','test','123456'),
       ('Phlebotomist','test','1234567'),
       ('Phlebotomist','test','12345');
GO
WITH RNs AS(
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY Code, Reason ORDER BY NEWID()) AS RN --PARTITION BY is a guess, NEWID as order is random as currently stands 
    FROM dbo.SampleData)
SELECT Code, Reason,
       MAX(CASE RN WHEN 1 THEN RNs.testValues END) AS BusinessUnit,
       MAX(CASE RN WHEN 2 THEN RNs.testValues END) AS MainUnit,
       MAX(CASE RN WHEN 3 THEN RNs.testValues END) AS [Location],
       MAX(CASE RN WHEN 4 THEN RNs.testValues END) AS CustID
FROM RNs
GROUP BY Code, Reason;
GO

GO
DROP TABLE dbo.SampleData;

不过,如果条目的顺序总是相同的话,这个问题很容易解决。添加 IDENTITY 列(您可以使用其他内容,但您需要 某物 要确定顺序,请执行以下操作:

CREATE TABLE dbo.SampleData(SomeID int IDENTITY(1,1), --Something to ORDER BY!
                            Code varchar(15),
                            Reason varchar(4),
                            testValues varchar(7));
GO
INSERT INTO dbo.SampleData (Code,
                            Reason,
                            testValues)
VALUES ('Phlebotomist','test','12345'),
       ('Phlebotomist','test','23456'),
       ('Phlebotomist','test',''),
       ('Phlebotomist','test','123456'),
       ('Phlebotomist','test','1234567'),
       ('Phlebotomist','test','12345');
GO
WITH RNs AS(
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY Code, Reason ORDER BY SomeID ASC) AS RN --NOw we have an order we can rely on!
    FROM dbo.SampleData)
SELECT Code, Reason,
       MAX(CASE RN WHEN 1 THEN RNs.testValues END) AS BusinessUnit,
       MAX(CASE RN WHEN 2 THEN RNs.testValues END) AS MainUnit,
       MAX(CASE RN WHEN 3 THEN RNs.testValues END) AS [Location],
       MAX(CASE RN WHEN 4 THEN RNs.testValues END) AS CustID
FROM RNs
GROUP BY Code, Reason;

GO
DROP TABLE dbo.SampleData;

别忘了,表中的数据存储在堆中。它没有“固有的秩序”。

为什么不看看这个中间选择呢?

select *, rank() over (partition by TestValues order by code) rank from
    (
        SELECT Code,Reason, 
        LTRIM(RTRIM(m.n.value('.[1]','varchar(500)'))) AS TestValues
        FROM
        (
        SELECT Code,Reason, CAST('<XMLRoot><RowData>' + REPLACE(TestValues,'##@##','</RowData><RowData>') + '</RowData></XMLRoot>' AS XML) AS x
        FROM dbo.Information where Logged_ID = 1001
        )t
        CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)
    ) r

答案就在 rank 价值-它是 1 每一排。这就是为什么只有团体 [1] 在您的数据透视中充满了数据(注意,有一个正确的 max 你定义的值;注意,最大值来自“字符串”而不是数字)。

partition by TestValues partition 重新开始对每个 TestValues 价值。”“分区”在这里是“组”的意思。每组排名,每组行号等等。如果分区应用于唯一列,那么每一行的rank/rk=1-因为ranking/rn将为每一行(每个分区)重新启动。

下面是应用于像您这样的数据集的不同窗口函数的示例: http://sqlfiddle.com/#!18/9eecb/31103

顺便说一下,“rank”函数有时应该为分区中的不同行给出相同的结果。看看两者的区别 等级 和 rn

我还想从 dbo.Information 使测试结果保持正确的顺序。按名称排序(对于组中的每个测试都是相同的)或按测试结果排序(与测试顺序无关)可能会为同一参数每次提供不同的输出,并且可能由于排序错误而不正确。