也许是 SQL Server PIVOT?-数据库问题

SQL Server PIVOT perhaps?(也许是 SQL Server PIVOT?)

本文介绍了也许是 SQL Server PIVOT?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

SELECT Name1, Name2, Value FROM mytable 给我以下结果集:

<前>名称 1 名称 2 值一个 P1 1一个 P2 1一个 P3 2乙 P1 3乙 P2 1乙 P4 1

我该如何翻译:

<前>甲乙P1 1 4P2 1 1P3 2 空P4 空 1

谢谢，

解决方案

您可以使用 PIVOT 子句.您的查询可能是这样的:

WITH Source as (选择名称 1、名称 2、[值]从 mytable)SELECT Name2, CASE WHEN A IS NOT NULL THEN A ELSE 'your string' END As A, CASE WHEN B IS NOT NULL THEN B ELSE 'your string' END As B从 (选择名称 2、名称 1、[值]发件人) s枢(MAX([Value]) FOR Name1 IN (A, B) -- 任何其他 Name1 都会放在这里) p

使用上面的示例数据，我的结果是

P1 1 3P2 1 1P3 2 你的弦P4 你的字符串 1

由于您的列数未知，因此您需要考虑使用动态 SQL，这里有几个关于 PIVOT 的答案.

SQL Server 2005 在未知列数上透视

透视表和连接列

SELECT Name1, Name2, Value FROM mytable gives me the following result set:

Name1 Name2 Value
A     P1     1
A     P2     1
A     P3     2
B     P1     3
B     P2     1
B     P4     1

How do I translate that to:

       A     B
P1     1     4
P2     1     1
P3     2     null
P4     null  1

Thanks,

解决方案

You can use a PIVOT clause. Your query could be something like this:

WITH Source as (
    SELECT Name1, Name2, [Value]
    FROM mytable 
)

SELECT Name2, CASE WHEN A IS NOT NULL THEN A ELSE 'your string' END As A
, CASE WHEN B IS NOT NULL THEN B ELSE 'your string' END As B
FROM (
        SELECT Name2, Name1, [Value]
        FROM Source
) s
PIVOT 
(
    MAX([Value]) FOR Name1 IN (A, B) -- any other Name1 would go here
) p

using your sample data above, my results were

P1  1           3
P2  1           1
P3  2           your string
P4  your string 1

EDIT:

Since you have an unknown number of columns, you will need to look at using dynamic SQL and there are several answers here on SO about that with PIVOT.

SQL Server 2005 Pivot on Unknown Number of Columns

Pivot Table and Concatenate Columns

这篇关于也许是 SQL Server PIVOT?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！