Dynamic pivot in SQL Server database

Question

I'm writing a query where there is pivot table that has to generated. Right now below is my code.

select *
from
(select [case owner], [time taken(minutes)] from StatusTable) as pivotdata
pivot(
sum([time taken(minutes)])
for [CASE OWNER] in
("XXX", "AAA", "BBB")
) as pivoting

But instead of giving the rows in for-in, I need to get this dynamically, I've seen a query here SQL Server dynamic PIVOT query? And modified my query to be

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

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME([case owner]) 
            FROM StatusTable c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'select *
from
(select [case owner], [time taken(minutes)] from StatusTable) as pivotdata
pivot(
sum([time taken(minutes)])
for [CASE OWNER] in
('+@cols+')
) as pivoting'

execute(@query)

And this is working fine, but the problem is that I've to use this query in my JDBC program. And without using execute(@query), it is not running in my SQL Server. Can I make this query similar to the first query, so that I can use the same in my program?

To execute a `dynamic query/SQL string` you need `EXECUTE` or `EXEC SP_EXECUTESQL` no other way — Pரதீப், May 18 '16 at 08:08
Without executing @query via SP_EXECUTESQL or EXECUTE (EXEC) commands, you have only a NVARCHAR type string variable. — Eralper, May 18 '16 at 11:10

score 0 · Answer 1 · answered Jun 30 '19 at 17:41

Microsoft's JDBC Driver for SQL Server (mssql-jdbc) supports the execution of an anonymous code block (multiple SQL statements) so we can just execute the same SQL code, including the EXECUTE(...) at the end:

String sql = " "
        + "SET NOCOUNT ON; "
        + "DECLARE @cols AS NVARCHAR(MAX), @query  AS NVARCHAR(MAX); "
        + " "
        + "SET @cols =  "
        + "    STUFF( "
        + "        ( "
        + "            SELECT distinct ',' + QUOTENAME([case owner])  "
        + "            FROM StatusTable c "
        + "            FOR XML PATH(''), TYPE "
        + "        ).value('.', 'NVARCHAR(MAX)'), "
        + "        1, "
        + "        1, "
        + "        ''); "
        + " "
        + "set @query = 'select * "
        + "from "
        + "( "
        + "    select [case owner], [time taken(minutes)] from StatusTable "
        + ") as pivotdata "
        + "pivot( "
        + "    sum([time taken(minutes)]) for [CASE OWNER] in ('+@cols+') "
        + ") as pivoting'; "
        +" "
        + "execute(@query); ";
Statement st = conn.createStatement();
ResultSet rs = st.executeQuery(sql);
ResultSetMetaData rsmd = rs.getMetaData();
// print column headings
for (int i = 1; i <= rsmd.getColumnCount(); i++) {
    System.out.printf("%5s", rsmd.getColumnLabel(i));
}
System.out.println();
rs.next();
// print column values
for (int i = 1; i <= rsmd.getColumnCount(); i++) {
    System.out.printf("%5s", rs.getString(i));
}
System.out.println();
/* console output:

   AAA  BBB  XXX
     2   13    1

 */

Dynamic pivot in SQL Server database

1 Answers1