Sql Server 2008 -无聚合功能的PIVOT

我知道你有很多关于这个的话题。但是，我还没有找到一个满足我的需求。我需要(按需)枢轴选择深表数据到一个宽的输出表。这里的问题是，我不能对Pivot使用聚合，因为它会吃掉输出中需要的响应。我已经想出了一个解决方案，但我不认为这是最好的，因为它需要无数个左连接才能工作。我把所有的尝试和笔记包括如下:

<>之前—Sql Server 2008数据库。深层表结构(不允许修改)包含userId为的名称/值对——外键。在许多情况下，可以有多个itemValue由用户为——itemName比如如果问他们的种族，可以回答White + Hispanic等。每个响应都被存储-作为一个单独的记录-目前不能更改。目标:在压缩结果的同时，将深层数据转向更宽的范围——坐下。对每个userId的所有项进行解释，并进行复制——column值(而不是show null)—样例表，用于存储单个和多个响应的一些数据声明@testTable为表(userId int, itemName varchar(50)， itemValue varchar(255))插入到@testTableSELECT 1， 'q01'， '1-q01 Answer'UNION SELECT 1， 'q02'， '1-q02 Answer'SELECT 1， 'q03'， '1-q03 Answer 1'SELECT 1， 'q03'， '1-q03 Answer 2'SELECT 1， 'q03'， '1-q03 Answer 3'UNION SELECT 1， 'q04'， '1-q04 Answer'SELECT 1， 'q05'， '1-q05答案'SELECT 2， 'q01'， '2-q01 Answer'UNION SELECT 2， 'q02'， '2-q02 Answer'SELECT 2， 'q03'， '2-q03 Answer 1'SELECT 2， 'q03'， '2-q03 Answer 2'SELECT 2， 'q04'， '2-q04 Answer'SELECT 2， 'q05'， '2-q05 '选择"原始数据"SELECT * FROM @TestTableSELECT '使用Pivot -显示每个itemName的itemValue的聚合结果-吃掉其他的';数据为(选择(标识), (itemName), (itemValue)从@testTable）选择(标识), (q02), (q03), (q05)从数据主(MIN(itemValue)——聚合函数吃掉需要的值。FOR itemName in ([q02]， [q03]， [q05])AS数据透视表SELECT 'Aggregate with Grouping - cause Null Values'选择明显的标识，[q02] = Max(CASE WHEN itemName = 'q02' THEN itemValue END)，[q03] = Max(CASE WHEN itemName = 'q03' THEN itemValue END)，[q05] = Max(CASE WHEN itemName = 'q05' THEN itemValue END)从@testTable在哪里itemName in ('q02'， 'q03'， 'q05')——让它更快一点集团userId——如果只有userId，它只给出1行。, (itemName), (itemValue)选择"多重左连接-工作正常，但如果旋转175列左右";数据为(选择用户标识, (itemName), (itemValue)从@testTable在哪里itemName in ('q02'， 'q03'， 'q05')——让它更快一点）选择不同的s1.userId，[q02] = s2.[itemValue]，[q03] = s3.[itemValue]，[q05] = 5.[itemValue]从数据s1左连接数据s2在s2。userId = s1.userId和s2。[itemName] = 'q02'左连接数据s3在s3。userId = s1.userId和s3。[itemName] = 'q03'左连接数据5s5。userId = s1.userId和s5。[itemName] = 'q05'之前

所以底部的查询是唯一一个(到目前为止)，我需要它做什么，但是左JOIN的将失去控制，并导致性能问题，当我使用实际的项目名称来支点。欢迎提出任何建议。

我认为您必须坚持使用连接，因为连接正是产生您所追求的结果的方式。连接的目的是将行集组合在一起(有条件或没有条件)，您的目标输出只不过是行子集的组合。

但是，如果大多数问题总是只有一个答案，那么您可以大大减少必要的连接数量。其思想是仅将多个响应组作为单独的行集进行连接。对于单响应项，它们仅作为目标项的整个数据集的一部分进行连接。

一个例子应该更好地说明我可能不善于口头描述的东西。假设源数据中有两个潜在的多响应组，'q03'和'q06'(实际上，这是源表:

DECLARE @testTable AS TABLE(
  userId int,
  itemName varchar(50),
  itemValue varchar(255)
);
INSERT INTO @testTable
SELECT 1, 'q01', '1-q01 Answer'
UNION SELECT 1, 'q02', '1-q02 Answer'
UNION SELECT 1, 'q03', '1-q03 Answer 1'
UNION SELECT 1, 'q03', '1-q03 Answer 2'
UNION SELECT 1, 'q03', '1-q03 Answer 3'
UNION SELECT 1, 'q04', '1-q04 Answer'
UNION SELECT 1, 'q05', '1-q05 Answer'
UNION SELECT 1, 'q06', '1-q06 Answer 1'
UNION SELECT 1, 'q06', '1-q06 Answer 2'
UNION SELECT 1, 'q06', '1-q06 Answer 3'
UNION SELECT 2, 'q01', '2-q01 Answer'
UNION SELECT 2, 'q02', '2-q02 Answer'
UNION SELECT 2, 'q03', '2-q03 Answer 1'
UNION SELECT 2, 'q03', '2-q03 Answer 2'
UNION SELECT 2, 'q04', '2-q04 Answer'
UNION SELECT 2, 'q05', '2-q05 Answer'
UNION SELECT 2, 'q06', '2-q06 Answer 1'
UNION SELECT 2, 'q06', '2-q06 Answer 2'
;

与原始帖子中的表相同，但添加了'q06'项)，结果脚本可能是这样的:

WITH ranked AS (
  SELECT
    *,
    rn = ROW_NUMBER() OVER (PARTITION BY userId, itemName ORDER BY itemValue)
  FROM @testTable
),
multiplied AS (
  SELECT
    r.userId,
    r.itemName,
    r.itemValue,
    rn03 = r03.rn,
    rn06 = r06.rn
  FROM ranked r03
    INNER JOIN ranked r06 ON r03.userId = r06.userId AND r06.itemName = 'q06'
    INNER JOIN ranked r ON r03.userId = r.userId AND (
      r.itemName = 'q03' AND r.rn = r03.rn OR
      r.itemName = 'q06' AND r.rn = r06.rn OR
      r.itemName NOT IN ('q03', 'q06')
    )
  WHERE r03.itemName = 'q03'
    AND r.itemName IN ('q02', 'q03', 'q05', 'q06')
)
SELECT userId, rn03, rn06, q02, q03, q05, q06
FROM multiplied
PIVOT (
  MIN(itemValue)  
  FOR itemName in (q02, q03, q05, q06)
) AS PivotTable

;使用SRData作为(SELECT——只查询此块中的单个响应项(标识), (q01), (q02), (q04), (q05)从@testTable主(分钟(itemValue)FOR itemName ([q01]， [q02]， [q04]， [q05])AS数据透视表）选择老(标识),老q01,老q02， [q03] = mr03.[itemValue],老q04,老q05， [q06] = mr06.[itemValue]从SRData老左连接@testTable mr03上mr03。userId = sr.userId AND mr03。itemName = 'q03'——对q03的Muli响应左JOIN @testTable mr06 ON mr06。userId = sr.userId AND mr06。itemName = 'q06'——q06的Muli响应

不清楚期望的结果应该是什么样子，但有一种可能性

; WITH Data AS (
    SELECT
        ROW_NUMBER() OVER (PARTITION BY [userId], [itemName] 
                               ORDER BY [itemValue]) AS RN
        ,  [userId]
        , [itemName]
        , [itemValue]
    FROM 
        @testTable
)
SELECT
    [userId]
    , [q02]
    , [q03]
    , [q05]
FROM
    Data
PIVOT
(
    MIN(itemValue)  
    FOR itemName in ([q02], [q03], [q05])
) AS PivotTable

userId      q02                            q03                            q05
----------- ------------------------------ ------------------------------ ------------------------------
1           1-q02 Answer                   1-q03 Answer 1                 1-q05 Answer
1           NULL                           1-q03 Answer 2                 NULL
1           NULL                           1-q03 Answer 3                 NULL
2           2-q02 Answer                   2-q03 Answer 1                 2-q05 Answer
2           NULL                           2-q03 Answer 2                 NULL

相关内容

最新更新

热门标签：