two sql query mix for one

Question

I want to combine two pieces of code into one, but I'm getting an error:

Part 1

SELECT idstd,namestd, idmajor, 
      c1, c2, c3, c4, c5, c6, 
      c7, c8, c9, c10,c11,c12,
      c13,c14,c15 
FROM 
    (SELECT status, idstd,namestd, idmajor, 
           'C' + cast(row_number() 
            OVER (partition BY idstd, idmajor 
               ORDER BY (SELECT 1)) AS varchar(10)) col
     FROM tbcheked) src 
PIVOT (MAX(status) FOR col IN (C1, C2, C3, C4, C5, 
                               C6, C7, C8, C9, C10,
                               c11,c12,c13,c14,c15)) piv

This code is for checking if the column status has values of present, absent or leave.

Example:

id    |   c1      |   c2     |     c3  |     c4  |     c5  |
1        present     absent    present   leave     present

I have the second piece of code for status count:

    SELECT idstd, 
           namemajor, 
           SUM(CASE WHEN status = 'present' 
                    THEN 1 
                    ELSE 0 
               END) AS present, 
           SUM(CASE WHEN status = 'absent' 
                    THEN 1 
                    ELSE 0 
               END) AS absent, 
           SUM(CASE WHEN status = 'leave' 
                    THEN 1 
                    ELSE 0 
                END) AS leave, 
   FROM tbcheked GROUP BY idstd, namemajor 
   ORDER BY idstd

Output:

id     | present  |  absent  |  leave  |
1          3           1          1

Now, I want to join both queries:

id    |   c1      |   c2     |     c3  |     c4  |     c5  |  present  |  absent  |  leave  |
1        present     absent    present   leave     present     3            1         1

Protip: If you have column names ending in `1`, `2`, `3`, etc. You're doing it wrong and need to [normalize your database](http://en.wikipedia.org/wiki/Database_normalization). — Jonathon Reinhart, Apr 24 '14 at 04:03

score 1 · Answer 1 · answered Apr 24 '14 at 05:12

1

Use join for conbining the output of both the queries.

select a.*, b.*
from (query 1)a join (query 2)b on a.id=b.id;

answered Apr 24 '14 at 05:12

G one

2,679
2
14
18

score 1 · Answer 2 · answered Apr 24 '14 at 05:17

Treat each part as a sub-query and join them:

select
    <whatever>
from
        (
            SELECT idstd,namestd, idmajor, 
                  c1, c2, c3, c4, c5, c6, 
                  c7, c8, c9, c10,c11,c12,
                  c13,c14,c15 
            FROM 
                (SELECT status, idstd,namestd, idmajor, 
                       'C' + cast(row_number() 
                        OVER (partition BY idstd, idmajor 
                           ORDER BY (SELECT 1)) AS varchar(10)) col
                 FROM tbcheked) src 
            PIVOT (MAX(status) FOR col IN (C1, C2, C3, C4, C5, 
                                           C6, C7, C8, C9, C10,
                                           c11,c12,c13,c14,c15)) piv
        ) as PivotedBit
INNER JOIN -- or OUTER, depending on your requirement
        (
            SELECT idstd, 
                   namemajor, 
                   SUM(CASE WHEN status = 'present' 
                            THEN 1 
                            ELSE 0 
                       END) AS present, 
                   SUM(CASE WHEN status = 'absent' 
                            THEN 1 
                            ELSE 0 
                       END) AS absent, 
                   SUM(CASE WHEN status = 'leave' 
                            THEN 1 
                            ELSE 0 
                        END) AS leave, 
            FROM tbcheked GROUP BY idstd, namemajor 
            --ORDER BY idstd -- cannot have this in a sub-query
        ) as SummingBit
    on SummingBit.idstd = PivotedBit.idstd
    -- and any other common keys
    ORDER BY idstd

It'll do two complete scans of tbcheked, so it won't be fast.

I have a hunch that adding dummy columns to the inner select of the PIVOT query using the CASE statements from the summing query, then adding SUM() to the PIVOT clause would be better. I haven't thought this through fully, however.

two sql query mix for one

2 Answers2