如何在没有聚集且不知道列值的情况下使用SQL来对表进行PIVOT? [英] How do I use SQL to PIVOT a table without an aggregate and without knowing column values?

问题描述

我正在处理旧版数据库，需要开发一个SQL查询以提供给客户.作为遗留数据库，它在设计时并未考虑此类查询.我简化了我需要选择的两个表，以使示例更易于理解.我有一个长桌"，需要使其变宽".我曾尝试使用PIVOT，但是遇到了两个问题:

I am working on a legacy database and need to develop a SQL query to give to a customer. As a legacy database, it was not designed with this type of query in mind. I've simplified the two tables I need to select from to make an easier to understand example. I have a "long table", and need to make it "wide". I have tried working with PIVOT but have encountered two issues:

1. 没有要聚合的东西-只是一个简单的矩阵变换.
2. 我不知道我需要添加多少列标题来减少列标题的实际值.

我需要一个SQL查询，该查询将针对以下给定架构输出如下结果:

| [Id] | [Author] | [PublishedYear] | [Title]   |
-------------------------------------------------
| 1    | 'Robert' | '2017'          | null      |
| 2    | 'Tim'    | null            | null      |
| 3    | null     | '2018'          | null      |
| 4    | null     | null            | 'Winning' |

要构建的SQL示例:

CREATE TABLE [Book] (
    [Id] int
);

INSERT INTO [Book] ([Id])
VALUES (1);

INSERT INTO [Book] ([Id])
VALUES (2);

INSERT INTO [Book] ([Id])
VALUES (3);

INSERT INTO [Book] ([Id])
VALUES (4);

CREATE TABLE [BookProperty] (
    [Name] VARCHAR(100),
    [Value] VARCHAR(100),
    [BookId] int
);

INSERT INTO [BookProperty] ([Name], [Value], [bookId])
VALUES ('Author', 'Robert', 1);

INSERT INTO [BookProperty] ([Name], [Value], [bookId])
VALUES ('Author', 'Tim', 2);

INSERT INTO [BookProperty] ([Name], [Value], [bookId])
VALUES ('PublishedYear', '2018', 3);

INSERT INTO [BookProperty] ([Name], [Value], [bookId])
VALUES ('PublishedYear', '2017', 1);

INSERT INTO [BookProperty] ([Name], [Value], [bookId])
VALUES ('Title', 'Winning', 4);

推荐答案

您可以尝试将条件汇总函数，MAX与CASE WHEN和GROUP BY

CREATE TABLE [Book] (
    [Id] int
);

INSERT INTO [Book] ([Id])
VALUES (1);

INSERT INTO [Book] ([Id])
VALUES (2);

INSERT INTO [Book] ([Id])
VALUES (3);

INSERT INTO [Book] ([Id])
VALUES (4);

CREATE TABLE [BookProperty] (
    [Name] VARCHAR(100),
    [Value] VARCHAR(100),
    [BookId] int
);

INSERT INTO [BookProperty] ([Name], [Value], [bookId])
VALUES ('Author', 'Robert', 1);

INSERT INTO [BookProperty] ([Name], [Value], [bookId])
VALUES ('Author', 'Tim', 2);

INSERT INTO [BookProperty] ([Name], [Value], [bookId])
VALUES ('PublishedYear', '2018', 3);

INSERT INTO [BookProperty] ([Name], [Value], [bookId])
VALUES ('PublishedYear', '2017', 1);

INSERT INTO [BookProperty] ([Name], [Value], [bookId])
VALUES ('Title', 'Winning', 4);

查询1 :

SELECT id,
       MAX(CASE WHEN Name = 'Author' THEN Value END) as 'Author',
       MAX(CASE WHEN Name = 'PublishedYear' THEN Value END) as 'PublishedYear',
       MAX(CASE WHEN Name = 'Title' THEN Value END) as 'Title'
FROM [Book] b INNER JOIN [BookProperty] bp
on b.Id = bp.BookId
GROUP BY id

结果 :

Results:

| id | Author | PublishedYear |   Title |
|----|--------|---------------|---------|
|  1 | Robert |          2017 |  (null) |
|  2 |    Tim |        (null) |  (null) |
|  3 | (null) |          2018 |  (null) |
|  4 | (null) |        (null) | Winning |

编辑

您可以尝试使用动态枢纽来实现期望.

You can try to use dynamic pivot to make you expect.

使用STUFF函数动态创建条件聚合函数执行语句，然后使用

use STUFF function dynamic create Condition Aggregate function execute statement then use execute dynamic excute your sql.

DECLARE @cols AS NVARCHAR(MAX),
        @query  AS NVARCHAR(MAX);



SET @cols = STUFF((SELECT DISTINCT ',MAX(CASE WHEN Name = '''  + Name  +''' THEN [Value] END) as ''' + Name + ''' '
             FROM [Book] b INNER JOIN [BookProperty] bp
             on b.Id = bp.BookId 
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')



set @query = 'SELECT ID, '+  @cols  + ' FROM [Book] b INNER JOIN [BookProperty] bp on b.Id = bp.BookId GROUP BY id'

execute(@query)

这篇关于如何在没有聚集且不知道列值的情况下使用SQL来对表进行PIVOT?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！