Unpivot and align rows for each field

Question

How can I align the rows and attributes of each field in a table to appear in the same line, using the given table as an example?. Mark this all fields in this table are varchar so as to enable uniformity.

STD	SUBJECT	MARKS	COMMENT	TDATE
ST1	MATH	25%	POOR	1/02/2021
ST1	ENGLISH	88%	DIST	2/02/2021
ST1	SCIENCE	56%	PASS	4/02/2021

The table should appear like this after unpivoting the contents in [Subject]:

STD	MATH	COMMENT	TDATE	ENGLISH	COMMENT	TDATE	SCIENCE	COMMENT	TDATE
ST1	25%	POOR	1/02/2021	88%	DIST	2/02/2021	56%	PASS	4/02/2021

I tried with my code and got lots of errors, so maybe I could get some help to achieve the desired result.

         SELECT
             [MATH],
             [ENGLISH],
             [SCIENCE]
         FROM (
             SELECT
                 STD, stdn,
                 cont,
                 x,
                 SUBJECT
             FROM
                 [dbo].[Exam]
             UNPIVOT (
                 x
                 for cont in (COMMENT, TDATE)
             ) a
        ) a
        PIVOT (
           MAX(x) 
           FOR SUBJECT IN (
                [MATH],
                [ENGLISH],
                [SCIENCE],
            )
        ) p
        WHERE p.stdn IN (SELECT STD FROM [dbo].[exam])

I assume this is mssql? (your db should be a tag!) but why is this tagged "join" "case" "sql-order-by"? — Paul Maxwell, Jun 16 '23 at 01:57
I edited the tags (*but I was really suggesting that you! do that*) Don't indulge in "tag graffitti" just add the tags that are relevant to your question. — Paul Maxwell, Jun 16 '23 at 02:54
Using `PIVOT`, or doing any kind of transposition in SQL, is an anti-pattern (because SQL is fundamentally row-based, not columnar) - ideally this kind of _presentation layer_ task should be done by the part of your software that actually renders the table on-screen for your users (i.e. your HTML or report-generator lib) - Otherwise, do you have a _good reason_ for wanting to do this? — Dai, Jun 16 '23 at 02:59
SQL Server is not well-suited to this because it expects that all columns getting pivotted/unpivotted are of exactly the same data type whereas you appear to be mixing strings (char/nchar) and dates here. This would be a task better suited to your application/frontend/reporting layer. — AlwaysLearning, Jun 16 '23 at 03:10
okay lets say i drop the tdate colum, how would i go about this problem — Miiro Bels, Jun 16 '23 at 03:38

score 1 · Answer 1 · answered Jun 16 '23 at 05:50

i think i solved it when i used MAX(CASE)

        SELECT [STD], 
         MAX(CASE WHEN SUBJECT = 'MATH' THEN MARKS ELSE '' END) MATH,
         MAX(CASE WHEN SUBJECT = 'MATH' THEN COMMENT ELSE '' END) COMMENT,
         MAX(CASE WHEN SUBJECT = 'MATH' THEN TDATE ELSE '' END) TDATE ,
   
         MAX(CASE WHEN SUBJECT = 'ENGLISH' THEN MARKS ELSE '' END) ENGLISH,
         MAX(CASE WHEN SUBJECT = 'ENGLISH' THEN COMMENT ELSE '' END) COMMENT,
         MAX(CASE WHEN SUBJECT = 'ENGLISH' THEN TDATE ELSE '' END) TDATE ,
    
         MAX(CASE WHEN SUBJECT = 'SCIENCE' THEN MARKS ELSE '' END) SCIENCE,
         MAX(CASE WHEN SUBJECT = 'SCIENCE' THEN COMMENT ELSE '' END) COMMENT,
         MAX(CASE WHEN SUBJECT = 'SCIENCE' THEN TDATE ELSE '' END) TDATE 

    FROM [dbo].[EXAM]  GROUP BY STD

Paul Maxwell · Answer 2 · 2023-06-16T04:38:17.580

Pivots for presentation purposes are not well suited to SQL and often better performed by a "front end" or "reporting tool" despite this, it is possible to pivot this data using T-SQL.

I assume that an unstated requirement is that the columns are presented in a specific left-to-right "block sequence" of "subject-mark-comment-tdate" so there needs to be a way to organize the columns in that sequence. A further probable requirement is that the pivot cater for an unknown number of total columns, for this you need "dynamic sql".

Note: This "unpivot" is complicated by the fact that there are different data types involved, one way to avoid this is to cast all data to strings or sql_variant (which is the approach seen below). Another (that I prefer) is to use JSON explained here but I wasn't also able to control the the "rnk" value as I needed for this query to formulate the numeric sequence of columns.

So, this is proposed:

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

SELECT @cols = STUFF((SELECT ',' + quotename(concat(c.column_name, cj.rn))
            from INFORMATION_SCHEMA.COLUMNS c
            cross join (
                select row_number() over(order by subject) rn
                from (select distinct subject from exam) e
                ) cj
            WHERE c.TABLE_NAME = 'Exam'
            AND c.column_name <> 'STD'
            order by cj.rn, c.ORDINAL_POSITION
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'');

SET @query = 'SELECT STD, ' + @cols + ' 
            from 
            (
                SELECT STD, 
                       concat(COLUMN_NAME,rnk) as COLUMN_NAME, 
                       VALUE
                FROM
                (
                    SELECT STD, 
                           CAST(SUBJECT AS sql_variant) AS SUBJECT, 
                           CAST(MARKS AS sql_variant) AS MARKS, 
                           CAST(COMMENT AS sql_variant) AS COMMENT, 
                           CAST(convert(varchar(10), TDATE ,120) AS sql_variant) AS TDATE,
                           dense_rank() over(partition by STD order by MARKS) as rnk
                    FROM Exam
                ) AS subquery
                UNPIVOT
                (
                    VALUE
                    FOR COLUMN_NAME IN (SUBJECT, MARKS, COMMENT, TDATE)
                ) AS unpvt
            ) x
            pivot 
            (
                max(VALUE)
                for COLUMN_NAME in (' + @cols + ')
            ) p ';

EXECUTE(@query);

STD	SUBJECT1	MARKS1	COMMENT1	TDATE1	SUBJECT2	MARKS2	COMMENT2	TDATE2	SUBJECT3	MARKS3	COMMENT3	TDATE3
ST1	MATH	25%	POOR	2021-02-01	SCIENCE	56%	PASS	2021-02-04	ENGLISH	88%	DIST	2021-02-02

see this as a working demo

nb: As I ended up converting the date column into a string to control the format, you could replace the "as sql_variant" with "as varchar(100)"(length of 100 is a guess)

Why not use the conditional case expression with aggregate. `MATH = max(case when SUBJECT = 'MATH' then MARKS end)`. It is much simpler. — Squirrel, Jun 16 '23 at 04:46

Unpivot and align rows for each field

2 Answers2