Search code examples
sql-serverunpivot

Unpivoting multiple columns displaying incorrect data and duplicates

I have a books table which has subject and chapterwise total pages and entered pages. Below is the screenshot of my table.

tbl_books

I am unpivoting this table based on the total pages and entered pages based on subject something like the one below

tbl_books_upv

Below is my query that I am using for unpivot

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT UID
       ,[Subject]
       ,TotalPages
       ,PagesEntered
    FROM ( SELECT UID
               ,[Subject]
               ,[Chapter1PagesEntered]
               ,[Chapter2PagesEentered]
               ,[Chapter1TotalPages]
               ,[Chapter2TotalPages]
            FROM [dbo].[tbl_books]
         ) AS SRC UNPIVOT ( TotalPages FOR Cities IN ( [Chapter1TotalPages],
                                                      [Chapter2TotalPages] ) ) AS upv UNPIVOT ( PagesEntered FOR Suppliers IN ( [Chapter1PagesEntered],
                                                              [Chapter2PagesEentered] ) ) AS upv
    WHERE RIGHT(TotalPages, 1) = RIGHT(PagesEntered, 1);

If you see the second screenshot the rows highlighted in orange box, the unpivot is giving me duplicate values something like cross product and also in the last the output values are not matching the original values. The first few rows are returning correct values however in the last and in middle its not.

For example for subject 101004, the total pages must be 24 24 for first row and 14 14 for the second row. Howver the query returns as 24 24 in first row, 24 14 in second row, 14 14 in third and 14 24 in fourth row.

Likewise Subject 103009 needs to return 15 12 in first row and 14 4 in the second. however i'm getting only one row with 14 4

I tried changing the WHERE RIGHT(TotalPages, 1) = RIGHT(PagesEntered, 1); however i'm not getting my desired output. I am not able to find what is causing this incorrect values.

I tried the codes provided in the below link however they are not solving my problem.

Unpivoting Multiple Columns

Unpivoting Multiple Columns

Here is my SQL Fiddle link. Any help is highly appreciated.

Thanks in advance

Solution

  • You can use the APPLY-Operator instead of UNPIVOT.

    For example:

    WITH CTE AS
(
SELECT r.* FROM (VALUES (1,'101001',16,14,16,14), 
                        (2,'101002',16,14,16,14),
                        (3,'101003',16,14,16,14),
                        (2,'101004',24,14,24,14)) AS r(UID,Subject,Chapter1PagesEntered,Chapter2PagesEntered,Chapter1TotalPages,Chapter2TotalPages)
)
SELECT CTE.UID, A.Chapter, A.Subject, A.PagesEntered, A.TotalPages
FROM CTE
CROSS APPLY (VALUES('Chapter1',UID,Subject,Chapter1PagesEntered, Chapter1TotalPages),
                   ('Chapter2',UID,Subject,Chapter2PagesEntered, Chapter2TotalPages)) AS A(Chapter,UID, Subject,PagesEntered,TotalPages);

    I need the CTE because I have no table like yours.