SQL Query with unknown number of columns

Question

I've written a query on a table which contains a list of 'Statements' against 'Companies' like so (obviously simplified):

Statement     | Company
---------------------------
ABC           | CompA
ABC           | CompB
DEF           | CompC

The query presents the information like so:

Statement    | CompA | CompB | Comp C
--------------------------------------
ABC          |   X   |   X   |
DEF          |       |       |   X

Using the code like this:

SELECT 
    [Requirement_Text],
    CASE WHEN(SUM(CASE WHEN Company = 'CompA' THEN 1 END)) IS NOT NULL THEN 'X' ELSE ' ' END AS CompA,
    CASE WHEN(SUM(CASE WHEN Company = 'CompB' THEN 1 END)) IS NOT NULL THEN 'X' ELSE ' ' END AS CompB,
    CASE WHEN(SUM(CASE WHEN Company = 'CompC' THEN 1 END)) IS NOT NULL THEN 'X' ELSE ' ' END AS CompC,
    CASE WHEN(SUM(CASE WHEN Company IS NULL THEN 1 END)) IS NOT NULL THEN 'X' ELSE ' ' END AS NILL
FROM [StatementTable]

Now, this is easy enough because we have a finite number of companies, but if we were to say move it down to Department level (instead of Company) then we have several more (an unknown number - N).

So the question is, how can I create the columns in the output table based on the number of distinct values in a given column of the input table?

For example:

Statement  | Company   | Department
---------------------------------------
ABC        | CompA     | Dept(1)
DEF        | CompA     | Dept(2)
DEF        | CompA     | Dept(3)
GHI        | CompA     | Dept(3)
ABC        | CompB     | Dept(N-1)
DEF        | CompC     | Dept(N)

Will become:

Statement  | Dept(1) | Dept(2) | Dept(3) | Dept(N-1) | Dept(N)
---------------------------------------------------------------
ABC        |    X    |         |         |     X     |     
DEF        |         |    X    |    X    |           |   X 
GHI        |         |         |    X    |           |

NOTE: In this case I've disregarded the company name.

Thanks in advance.

Check http://stackoverflow.com/questions/12004603/mysql-pivot-row-into-dynamic-number-of-columns — Damodaran, Jan 07 '14 at 06:58
Which RDBMS, MySQL or SQL Server? In either case, this is essentially a duplicate (which one depend on which db and _version_, probably). — Clockwork-Muse, Jan 07 '14 at 07:22
The sql language standard expects you to know the number and types of the columns you want in the output. :/ You can find ways around this (namely, dynamic sql), but you'll have better luck trying to pivot your data on the client. — Joel Coehoorn, Jan 07 '14 at 07:56

score 0 · Accepted Answer · edited May 23 '17 at 11:56

0

you should use Dynamic query and Pivote :

Check This out

and you can see the document here : Check this

edited May 23 '17 at 11:56

Community

1
1

answered Jan 07 '14 at 07:40

Pouya Samie

3,718
1
21
34

SQL Query with unknown number of columns

1 Answers1