枢轴案例什么时候? [英] Pivot case when?

问题描述

我有以下数据表:

<前>id displayName stringValue dateValue59FAA56C-4C0C-456E-BA68-AC63250D6281 用户 SID SID-122 NULLEBD6F18D-3CD3-4134-8FFB-7620D3EA93DF 用户 SID SID2 NULL59FAA56C-4C0C-456E-BA68-AC63250D6281 用户名 我的用户 NULLEBD6F18D-3CD3-4134-8FFB-7620D3EA93DF 用户名 User 2 NULL59FAA56C-4C0C-456E-BA68-AC63250D6281 上次登录 NULL 2012-01-01EBD6F18D-3CD3-4134-8FFB-7620D3EA93DF 上次登录 NULL 2012-01-10

我想变成:

<前>id [用户 SID] [用户名] [上次登录]59FAA56C-4C0C-456E-BA68-AC63250D6281 SID-122 我的用户 2012-01-01EBD6F18D-3CD3-4134-8FFB-7620D3EA93DF SID2 用户 2 2012-01-10

我可以通过使用以下支点来部分工作:

SELECT id, [User SID], [User Name], [Last Login] from(选择身份证， 字符串值， 显示名称来自#TestTable) X枢(最大(字符串值)对于 displayName in ([User SID], [User Name], [Last Login])) p

除了 lastLogin 被填充为 NULL(这是有道理的，因为它没有包含在 MAX 中的任何地方).

现在，我尝试将枢轴更改为:

 枢轴(MAX(ISNULL(stringValue,dateValue))对于 displayName in ([User SID], [User Name], [Last Login])) p

还有:

 枢轴(MAX(CASE WHEN stringValue IS NULL THEN dateValue ELSE stringValue END)对于 displayName in ([User SID], [User Name], [Last Login])) p

但这些都不是有效的 sql.关于如何使其正常工作的任何建议?

解决方案

你需要使用 PIVOT 吗?因为一个简单的方法是使用 CASE表达式:

SELECT id,MIN(CASE WHEN displayName = 'User SID' THEN stringValue END) [User SID],MIN(CASE WHEN displayName = 'User Name' THEN stringValue END) [用户名],MIN(CASE WHEN displayName = 'Last Login' THEN dateValue END) [Last Login]从你的桌子按 ID 分组

结果如下:

╔==================================╦====＝＝＝＝＝＝╦＝＝＝＝＝＝＝＝＝＝＝＝╦＝＝＝＝＝＝＝＝＝＝＝＝╗║ ID ║ 用户 SID ║ 用户名 ║ 上次登录 ║╠==================================╬==========╬＝＝＝＝＝＝＝＝＝＝＝╬＝＝＝＝＝＝＝＝＝＝＝＝＝╣║ EBD6F18D-3CD3-4134-8FFB-7620D3EA93DF ║ SID2 ║ 用户 2 ║ 2012-01-10 ║║ 59FAA56C-4C0C-456E-BA68-AC63250D6281 ║ SID-122 ║ 我的用户 ║ 2012-01-01 ║╚＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝╩＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝╩＝＝＝＝＝＝＝＝＝╩==========╩===========╝

这里有一个 sqlfiddle 带有演示你试试.

I have the following data table:

id                                      displayName stringValue     dateValue
59FAA56C-4C0C-456E-BA68-AC63250D6281    User SID    SID-122         NULL
EBD6F18D-3CD3-4134-8FFB-7620D3EA93DF    User SID    SID2            NULL
59FAA56C-4C0C-456E-BA68-AC63250D6281    User Name   My User         NULL
EBD6F18D-3CD3-4134-8FFB-7620D3EA93DF    User Name   User 2          NULL
59FAA56C-4C0C-456E-BA68-AC63250D6281    Last Login  NULL            2012-01-01
EBD6F18D-3CD3-4134-8FFB-7620D3EA93DF    Last Login  NULL            2012-01-10

That I would like to turn into:

id                                      [User SID]  [User Name] [Last Login]
59FAA56C-4C0C-456E-BA68-AC63250D6281    SID-122     My User     2012-01-01
EBD6F18D-3CD3-4134-8FFB-7620D3EA93DF    SID2        User 2      2012-01-10

Which I can get to partially work by using the following pivot:

SELECT id, [User SID], [User Name], [Last Login] from 
            (
                select id
                    , stringValue
                    , displayName
                from #TestTable
           ) x
            pivot 
            (
                 MAX(stringValue)
                for displayName in ([User SID], [User Name], [Last Login])
            ) p 

Except that lastLogin gets populated with NULL (which makes sense, because its not included in the MAX anywhere).

Now, I have tried to change the pivot to be:

            pivot 
            (
                 MAX(ISNULL(stringValue,dateValue))
                for displayName in ([User SID], [User Name], [Last Login])
            ) p 

And also:

            pivot 
            (
                 MAX(CASE WHEN stringValue IS NULL THEN dateValue ELSE stringValue END)
                for displayName in ([User SID], [User Name], [Last Login])
            ) p 

But neither of those are valid sql. Any suggestions on how I could get this to work properly?

解决方案

Do you need to use PIVOT?, because a simple way for this would be to use CASE expressions:

SELECT  id,
        MIN(CASE WHEN displayName = 'User SID' THEN stringValue END) [User SID],
        MIN(CASE WHEN displayName = 'User Name' THEN stringValue END) [User Name],
        MIN(CASE WHEN displayName = 'Last Login' THEN dateValue END) [Last Login]
FROM YourTable
GROUP BY id

Here is the result:

╔══════════════════════════════════════╦══════════╦═══════════╦════════════╗
║                  ID                  ║ USER SID ║ USER NAME ║ LAST LOGIN ║
╠══════════════════════════════════════╬══════════╬═══════════╬════════════╣
║ EBD6F18D-3CD3-4134-8FFB-7620D3EA93DF ║ SID2     ║ User 2    ║ 2012-01-10 ║
║ 59FAA56C-4C0C-456E-BA68-AC63250D6281 ║ SID-122  ║ My User   ║ 2012-01-01 ║
╚══════════════════════════════════════╩══════════╩═══════════╩════════════╝

And here is a sqlfiddle with a demo for you to try.

这篇关于枢轴案例什么时候?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！