sql server - How do I use SQL to PIVOT a table without an aggregate and without knowing column values?

Question

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. There is nothing to aggregate- it's just a simple matrix transformation.
2. I don't know the number of columns I need to add much less the actual values of the column headers.

I need a SQL query that will output the results as follows for the given schema below:

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

SQL to Build Example:

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);

Answer 1

You can try to use Condition Aggregate function, MAX with CASE WHEN and 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);

Query 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 |

EDIT

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

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)