Dynamic table creation in SQL Server

Question

I have a situation like this

+---------+-----------+------------+
| FieldNo | FieldName | Substring  |
+---------+-----------+------------+
|       1 | A         | 8          |
|       1 | A         | A          |
|       1 | A         | DC         |
|       2 | B         | 7          |
|       3 | C         | 22         |
|       3 | C         | 37         |
+---------+-----------+------------+

Need output like this:

+----+------+------+
| A  |  B   |  C   |
+----+------+------+
| 8  | 7    | 22   |
| A  | Null | 37   |
| DC | Null | Null |
+----+------+------+

Any suggestions how I can do this in SQL Server?

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

select @cols = STUFF((SELECT ',' + QUOTENAME(Field_name) 
                    from bear_crossjoin
                    group by FIELD_NAME, FIELDNUMBER
                    order by FIELDNUMBER
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = N'SELECT ' + @cols + N' from 
             (
                select substring, Field_name
                from bear_crossjoin
            ) x
            pivot 
            (
                max(substring)
                for field_name in (' + @cols + N')
            ) p '

exec sp_executesql @query

Just search for `SQL Server` and `PIVOT`. Feel free to ask if you have *specific* questions — Panagiotis Kanavos, Dec 11 '14 at 14:06
I need fieldname A, B, C on top. The above example gives it as a row not as a column. — Arun.K, Dec 11 '14 at 14:07
*Did* you check any of the similar questions about PIVOT? Did you have a problem with them? — Panagiotis Kanavos, Dec 11 '14 at 14:08
@Arun.K Take a look at my answer [here](http://stackoverflow.com/questions/18851588/pivot-rows-to-columns-with-more-than-1-value-returned/18851868#18851868) You'll need to use `row_number()` when pivoting the data to return more than one row for each `fieldname`. — Taryn, Dec 11 '14 at 14:15
Thanks bluefeet. My substring values are dyanamic, sorry about the confusion. Since there are around 970 field names it would be difficult to mention it one by one like here "FieldName IN ([A], [B], [C]))" Any ways to dynamically populate the fieldnames. — Arun.K, Dec 11 '14 at 14:26
@Arun.K, what will be the consuming application/technology for this? SSRS has a nice dynamic table control that will do what you are asking. — Jeremy, Dec 11 '14 at 14:30
@Panagiotis Kanavos all the examples i see are pivot for static values not dynamically on fieldnames and values. — Arun.K, Dec 11 '14 at 14:30
@Arun.K You can utilize the `row_number()` trick along with my answer [here](http://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server/15745076#15745076) that includes a dynamic solution. Basically, you'll need to create a sql string that will be executed giving you the final result. — Taryn, Dec 11 '14 at 14:32
@bluefeet tried your dynamic query in the link provided. Its throwing error while executing - Must declare the scalar variable "@query". — Arun.K, Dec 11 '14 at 14:52
@Arun.K How about edit your question with the code? Did you declare the variable? — Taryn, Dec 11 '14 at 14:53
@bluefeet sorry i am new to SQL, primarily a ETL guy. I did like this - — Arun.K, Dec 11 '14 at 14:56
@Arun.K - code works with minor changes - see this demo -- http://sqlfiddle.com/#!3/5396ed/5 — Taryn, Dec 11 '14 at 15:03
glad things worked out for you. Mark one of the answers below as answer and close the post :) — Codeek, Dec 11 '14 at 15:06
@bluefeet great! it works in SQL, i have to pull that in my SAP tool. Thanks much! — Arun.K, Dec 11 '14 at 15:12
@Codeek, ideally i have to give correct answer to yours and bluefeets post. — Arun.K, Dec 11 '14 at 15:13
Hi a follow up to this thread, how can i select this into a a table? I tried openrowset but security privilages are blocking it. Any other suggestions? — Arun.K, Dec 11 '14 at 16:23

Codeek · Accepted Answer · 2014-12-11T14:58:41.770

3

I've assumed the table name as tblTemp. Try this

DECLARE @colList NVARCHAR(max)
DECLARE @query NVARCHAR(max)

select @colList = coalesce(@colList + ',', '') +  convert(varchar(12),TT.FIELDNAME)
from (SELECT DISTINCT FIELDNAME FROM tblTemp) TT
order by TT.FIELDNAME


SET @query = 'SELECT ' + @colList + ' FROM
(
SELECT row_number() over(partition by FIELDNO
                        order by FIELDNO) seq,
       FieldName, [Substring]
FROM tblTemp ) als
PIVOT
( MAX([SUBSTRING])
  FOR FieldName IN (' + @colList + ')
) piv'

exec sp_executesql @query

fiddle

edited Dec 11 '14 at 14:58

answered Dec 11 '14 at 14:48

Codeek

1,624
1
11
20

throwing error Must declare the scalar variable "@results". – Arun.K Dec 11 '14 at 14:54
@Arun.K If you don't show the code you are using no one will be able to help. Where are you getting `@results`? This answer doesn't even include that variable. – Taryn Dec 11 '14 at 14:55
my bad, I had used @results first, but changed it to colList for better understanding. EDITS MADE : VARCHAR to NVARCHAR, and a space AFTER : query = 'SELECT ' – Codeek Dec 11 '14 at 15:02

Jason W · Answer 2 · 2014-12-11T15:27:31.147

2

It seems you are trying to PIVOT by row number instead of directly on the field names. The approach below pivots using the row number to get the desired output:

DECLARE @Data TABLE (FieldNo INT, FieldName VARCHAR(50), [Substring] VARCHAR(500))
INSERT @Data VALUES
    (1, 'A', '8'),
    (1, 'A', 'A'),
    (1, 'A', 'DC'),
    (2, 'B', '7'),
    (3, 'C', '22'),
    (3, 'C', '37')

;WITH DataRows AS (
    SELECT
        FieldName,
        [Substring],
        ROW_NUMBER() OVER (PARTITION BY FieldNo ORDER BY FieldName, [Substring]) AS RowNum
    FROM @Data
)
    SELECT
        CONVERT(VARCHAR(10), [A]) AS [A],
        CONVERT(VARCHAR(10), [B]) AS [B],
        CONVERT(VARCHAR(10), [C]) AS [C]
    FROM DataRows
        PIVOT (MAX([Substring]) FOR FieldName IN ([A], [B], [C])) T

This yields the desired output:

A          B          C
---------- ---------- ----------
8          7          22
A          NULL       37
DC         NULL       NULL

edited Dec 11 '14 at 15:27

answered Dec 11 '14 at 14:16

Jason W

13,026
3
31
62

Jason, what should i mention here FOR FieldName IN ([A], [B], [C])) T because i have around 978 columns. – Arun.K Dec 11 '14 at 14:38
Did you just say 978 COLUMNS?, if yes, use dynamic sql. That 's the only option I guess – Codeek Dec 11 '14 at 14:39
Yes 978 columns. I need a dynamic sql, what i have given as an example is only few. Any suggestions for a dynamic sql? – Arun.K Dec 11 '14 at 14:44
see my answer for dynamic sql and pivot – Codeek Dec 11 '14 at 14:51

Dynamic table creation in SQL Server

2 Answers2

Linked