Is there a way to upivot the folllowing table dynamically

Question

Is there a way to upivot the folllowing table dynamically

Resender

SSCertifiable

Points: 7443
More actions
September 25, 2015 at 9:50 am

#311785

Let's assume table dbo.SO
CREATE TABLE [dbo].[SO](
[Column 0] [varchar](50) NULL,
[Column 1] [varchar](50) NULL,
[Column 2] [varchar](50) NULL,
[Column 3] [varchar](50) NULL,
[Column 4] [varchar](50) NULL,
[Column 5] [varchar](50) NULL,
[Column 6] [varchar](50) NULL,
[Column 7] [varchar](50) NULL,
[Column 8] [varchar](50) NULL,
[Column 9] [varchar](50) NULL,
[Column 10] [varchar](50) NULL,
[Column 11] [varchar](50) NULL,
[Column 12] [varchar](50) NULL,
[Column 13] [varchar](50) NULL,
[Column 14] [varchar](50) NULL,
[Column 15] [varchar](50) NULL
Column 0 contains names, Column 1 => 15 values.
The first row/record Column 1 => 15 contain months/year combinations
The rest of the rows/records Column 1 => 15 contain values for those months for the name in Column 0
Now I need to get the following
I the first column I need the name,in the second the value found in the first row and then I want the value.
I found several explanations on unpivot but none that could solve my issue.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

I'm not sure if you're familiar with the method explained in this article: http://www.sqlservercentral.com/articles/CROSS+APPLY+VALUES+UNPIVOT/91234/

Here's a way to use it and create a dynamic pivot.

DECLARE @sql nvarchar(max);

SELECT @sql = 'SELECT SO.[Column 0], u.Name, u.Value FROM SO CROSS APPLY (VALUES' +

STUFF( (SELECT ',(' + QUOTENAME( COLUMN_NAME, '''') + ',' + QUOTENAME( COLUMN_NAME) + ')'

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME = 'SO'

AND ORDINAL_POSITION > 1

FOR XML PATH('')), 1, 1, '') + ')u(Name,Value);';

EXEC sp_executesql @sql;

It relies on the position of your columns, but you can change that in the WHERE clause.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Resender SSCertifiable Points: 7443 More actions · Answer 2

Sorry for my late response & txn Luis Cazares, your suggestion/solution allowed me to create exactly what I wanted, many txn.

ben.brugman SSChampion Points: 13369 More actions · Answer 3

Hello Luis Cazaras and others:

This afternoon I got a request for a Pivot 'script', which would adjust to changes in a table. So I remembered the given solution on the forum.

So I went to the forum and tried the solution.

Problem: Varchar colums, dit not process.

So I did some alterations to the example.

I still have to grasp the CROSS APPLY, Stuff and For XML parts of the script.

Could you please take a look at the alterations I made.

Thanks for your time and attention,

Ben

-------------------------------------------------------------------------------------------------------

-- 20151001

-- ben brugman

-- Thanks for your example Luis Cazaras :

-- http://www.sqlservercentral.com/Forums/Topic1723088-391-1.aspx

--

-- Adjustments made.

-- 1. Standard conversion to varchar. Varchar koloms produced an error.

-- 2. Tablename has been parameterised. (Has to be supplied once)

-- 3. First column_name is taken from the information schema.

-- Alternatives Do not show any 'standard' columns.

-- Use another column, or multiple other columns. (PK for example).

-- 4. In the result now only one row is shown by the statement TOP(NNNNNN)

-- Alternatives : This can be left out and done in the end result (the temptable).

-- 5. Show all fields : This can be changed changing : 'AND ORDINAL_POSITION > 0 '

-- Alternative : Do not show the fields which are allready shown. (PK for example).

-- Reduce the number of fields shown.

-- 6. Result in a temptable : ##stemp

-- If sp_drop does not exist use another way to drop an existing table.

-- Alternative : Direct output, qoute out the line : 'INTO ##stemp'

--

-- User adjustable.

--

DECLARE @table_name varchar(300) = 'AA_Added_Constraint'

exec sp_drop ##stemp -- result table

--

-- IF sp_drop does not exist, use:

-- drop table ##stemp

DECLARE @sql nvarchar(max);

DECLARE @first_column varchar(300) = 'xxxxxxx'

DECLARE @number_of_columns int =3

Select @first_column = column_name from information_schema.COLUMNS where TABLE_NAME = @table_name and ORDINAL_POSITION = 1

Select @number_of_columns = COUNT(*) from information_schema.COLUMNS where TABLE_NAME = @table_name

SELECT @sql =

'

SELECT

top(NNNNNN)

TTTTTT.[CCCCCC] -- Use column(s) to indicate which row.

, u.Name

, u.Value

INTO ##stemp -- Place result in a temporary table.

FROM TTTTTT CROSS APPLY (VALUES' +

STUFF( (SELECT ',(' + QUOTENAME( COLUMN_NAME, '''') + ',' +

'CONVERT(VARCHAR(300),' + QUOTENAME( COLUMN_NAME) + ')' + ')'

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME = @table_name

AND ORDINAL_POSITION > 0 -- Define which columns to use.

FOR XML PATH('')), 1, 1, '') + ')u(Name,Value);';

Print @sql

SET @sql = REPLACE(@SQL, 'TTTTTT',@table_name)

SET @sql = REPLACE(@SQL, 'CCCCCC',@first_column)

SET @sql = REPLACE(@SQL, 'NNNNNN',@number_of_columns)

-- Print @sql

EXEC sp_executesql @sql;

-- select Name, value from ##stemp

select * from ##stemp

-- exec sp_drop ##stemp -- remove the result table

-------------------------------------------------------------------------------------------------------