en-UShe-IL
You are here:   Blog
Register   |  Login

Blog Archive:

Maximize
* Can be used in order to search for older blogs Entries

Search in blogs


Blog Categories:

Maximize
* Can be used in order to search for blogs Entries by Categories

Blog Tags:

Maximize
* Can be used in order to search for blogs by keywords

TNWikiSummit


Awared MVP

 


Microsoft® Community Contributor 


Microsoft® Community Contributor


 Read first, before you you use the blog! Maximize

Recent Entries

Minimize
ספט25

Written by: ronen ariely
25/09/2011 19:03 RssIcon

קוד זה מהווה תיקון לקוד שהוצג בבלוג  ברשת בקישור הבא:

http://sqlwithmanoj.wordpress.com/2011/01/25/dynamic-pivot/#comment-299

* הערה לגבי השגיאה הועברה לכותב הקוד ובינתיים אני אציג כאן את התיקון

יש לציין שמדובר בתיקון קטן של שורה אחת בלבד!

 

/*********************************************************************/

/*************************************************** Dynamic PIVOT ***/

/*********************************************************************/

USE [QQ]

GO

 

/*********************************************************************/

-- Create test tables

create table DynamicPivotTbl01 (number int, [desc] varchar(20),

location int, numberatlocation int)

create table DynamicPivotTbl02 (code int, name varchar(20))

 

-- Insert test data

insert into DynamicPivotTbl01 values (12345,'test',1000,5)

insert into DynamicPivotTbl01 values (12345,'test',1001,2)

insert into DynamicPivotTbl01 values (12345,'test',1002,4)

insert into DynamicPivotTbl01 values (12345,'test',1003,9)

insert into DynamicPivotTbl01 values (12345,'test',1004,7)

insert into DynamicPivotTbl01 values (12345,'test',1005,3)

 

insert into DynamicPivotTbl02 values (1000,'loc1')

insert into DynamicPivotTbl02 values (1001,'loc2')

insert into DynamicPivotTbl02 values (1002,'loc3')

insert into DynamicPivotTbl02 values (1003,'loc4')

insert into DynamicPivotTbl02 values (1004,'loc5')

insert into DynamicPivotTbl02 values (1005,'loc6')

 

insert into DynamicPivotTbl01 values (1234,'test',100,5)

insert into DynamicPivotTbl01 values (1234,'test',101,2)

insert into DynamicPivotTbl01 values (1234,'test',102,4)

insert into DynamicPivotTbl01 values (1234,'test',103,9)

insert into DynamicPivotTbl01 values (1234,'test',104,7)

insert into DynamicPivotTbl01 values (1234,'test',105,3)

 

insert into DynamicPivotTbl02 values (100,'loc1')

insert into DynamicPivotTbl02 values (101,'loc2')

insert into DynamicPivotTbl02 values (102,'loc3')

insert into DynamicPivotTbl02 values (103,'loc4')

insert into DynamicPivotTbl02 values (104,'loc5')

insert into DynamicPivotTbl02 values (105,'loc6')

 

GO

/*********************************************************************/

 

-- Static PIVOT

select number, [desc], [loc1], [loc2], [loc3], [loc4], [loc5]

from (select number, [desc], numberatlocation, name

from DynamicPivotTbl01 join DynamicPivotTbl02 on DynamicPivotTbl01.location=DynamicPivotTbl02.code)p

PIVOT(MAX (numberatlocation) FOR Name IN ( [loc1], [loc2], [loc3], [loc4], [loc5] )

) AS pvt

ORDER BY number

 

 

-- Dynamic PIVOT

declare @col varchar(1000)

declare @sql varchar(2000)

 

select @col = COALESCE(@col + ', ','') + QUOTENAME(name)

--from DynamicPivotTbl02

from (select distinct name from DynamicPivotTbl02) Tbl

select @col

 

-- Now setting this @col variable in the Dynamic SQL.

set @sql = '

select number, [desc], ' + @col + '

from (select number, [desc], numberatlocation, name

from DynamicPivotTbl01 join DynamicPivotTbl02 on DynamicPivotTbl01.location=DynamicPivotTbl02.code)p

PIVOT(MAX (numberatlocation) FOR Name IN ( ' + @col + ' )

) AS pvt

ORDER BY number'

 

--print @sql

exec (@sql)