Project row details as columns in SQL

Question

I have two tables for the library schema

Table Book(BookID(Primary Key & Identity), Name, Author, Available)
Table BookDetails(BookDetailsID(Primary key & Identity), BookID(Foreign key for BookID),AttributeID(Foreign Key for Attribute),AttributeValue)
Table BookAttributes(AttributeID,Name)

So, the Book table just contains basic details and BookDetails table contains more finer details like ISBN, Publication, Pages etc..

I want a report of all the books with their attributes, I am able to get the Attributedetails of the book but not sure how to project them as columns in my result set. i am looking for something like following

BookID     BookName      ISBN  Publication  .....
-----------------------------------------------------
10         HarryPotter1  xxxx  xxxxxx 
20         HarryPotter2  xxxx  xxxxxx
30         HarryPotter3  xxxx  xxxxxx
40         HarryPotter4  xxxx  xxxxxx

where ISBN and Publication etc are coming from BookDetails table where they are stored as separate rows for wach ISBN and Publication attributes keyed by Book ID. How do I project the Attribute values which are rows in BookDetails table into columns in my result set.

Possible duplicate of [Efficiently convert rows to columns in sql server](https://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server) — Ruskin, Mar 09 '18 at 06:00

tysonwright · Answer 1 · 2018-03-15T20:12:35.997

Ok, so you need a PIVOT to do this, and dynamic SQL (this figures out what the columns are supposed to be, then creates the SQL based on that, then executes that SQL). I think this will work:

DECLARE
    @Columns NVARCHAR(MAX),
    @SQL_Statement NVARCHAR(MAX)

SELECT  @Columns = ISNULL( @Columns + ', ', '') + '[' + b.[Name] + ']' FROM (SELECT DISTINCT [Name] FROM BookAttributes) AS b

SELECT @SQL_Statement = '
WITH cte_Flat AS
(
SELECT
    b.BookID,
    bd.AttributeValue
    ba.[Name]
FROM 
    Book AS b
JOIN
    BookDetails AS bd
ON  
    b.BookID = bd.BookID
JOIN
    BookAttributes AS ba
ON
    bd.AttributeID = ba.AttributeID
)
SELECT
    BookID,
    ' + @Columns + '
FROM
    cte_Flat
PIVOT
    (
      MAX(AttributeValue)
      for [Name] in (' + @Columns + ')
    ) AS u'

EXEC SP_EXECUTESQL  @SQL_Statement = @SQL_Statement

agreed, but this just gives the book attributes as rows, meaning for each book you have a row for publication, ISBN etc... i am looking for Book Attributes names as column , like my example in question, where publication , ISBN are actual columns but not rows — Programmerzzz, Mar 09 '18 at 01:06

score 0 · Answer 2 · edited Mar 15 '18 at 21:16

Ok, so you need a PIVOT to do this, and dynamic SQL (this figures out what the columns are supposed to be, then creates the SQL based on that, then executes that SQL). I think this will work:

DECLARE
    @Columns NVARCHAR(MAX),
    @SQL_Statement NVARCHAR(MAX)

SELECT  @Columns = ISNULL( @Columns + ', ', '') + '[' + b.[Name] + ']' 
FROM (SELECT DISTINCT [Name] FROM BookAttributes) AS b

SELECT @SQL_Statement = '
WITH cte_Flat AS
(
    SELECT
        b.BookID,
        bd.AttributeValue
        ba.[Name]
    FROM Book AS b
        JOIN BookDetails AS bd ON b.BookID = bd.BookID
        JOIN BookAttributes AS ba ON bd.AttributeID = ba.AttributeID
)
SELECT
    BookID,
    ' + @Columns + '
FROM
    cte_Flat
PIVOT
    (
      MAX(AttributeValue)
      for [Name] in (' + @Columns + ')
    ) AS u'

EXEC SP_EXECUTESQL  @SQL_Statement = @SQL_Statement

This is as good as the other answer as well, but like I said, I want the Book row with the Attribute Names as columns and their values as column values for that book row..like I mentioned in my example in question — Programmerzzz, Mar 09 '18 at 05:36

Project row details as columns in SQL

2 Answers2