Click here to Skip to main content
15,881,380 members
Home / Discussions / Database
   

Database

 
AnswerRe: SQL Server 2005 on Windows Vista Pin
Alsvha7-Jul-08 22:53
Alsvha7-Jul-08 22:53 
Questionsql query Pin
Pankaj Garg7-Jul-08 2:52
Pankaj Garg7-Jul-08 2:52 
AnswerRe: sql query Pin
leoinfo7-Jul-08 3:43
leoinfo7-Jul-08 3:43 
QuestionCreate a table with columns' name as primary columns data Pin
prithaa7-Jul-08 1:57
prithaa7-Jul-08 1:57 
AnswerRe: Create a table with columns' name as primary columns data Pin
ChandraRam7-Jul-08 2:14
ChandraRam7-Jul-08 2:14 
GeneralRe: Create a table with columns' name as primary columns data Pin
prithaa7-Jul-08 3:01
prithaa7-Jul-08 3:01 
GeneralRe: Create a table with columns' name as primary columns data Pin
ChandraRam7-Jul-08 3:04
ChandraRam7-Jul-08 3:04 
AnswerRe: Create a table with columns' name as primary columns data Pin
leoinfo7-Jul-08 8:14
leoinfo7-Jul-08 8:14 
Hi,

Here is a working code for your case using <big>PIVOT</big> and FOR XML PATH.
The list of columns is built with FOR XML PATH
Pivot columns are created <big>dynamically</big>, so doesn’t really matter how many columns (in your case – how many products) you have.

I hope this solve your problem. Smile | :)

CREATE TABLE #p (ProdID int, ProdName nvarchar(20)); 
CREATE TABLE #s (TimePeriod int, Qty int, ProdID int); 

insert into #p (ProdID, ProdName) select 1, 'Shirts'; 
insert into #p (ProdID, ProdName) select 2, 'Trousers'; 
insert into #p (ProdID, ProdName) select 3, 'Tie'; 
insert into #p (ProdID, ProdName) select 4, 'Belts'; 

insert into #s (TimePeriod, Qty, ProdID) select 20080705,138,1; 
insert into #s (TimePeriod, Qty, ProdID) select 20080706,539,1; 
insert into #s (TimePeriod, Qty, ProdID) select 20080707,313,1; 
insert into #s (TimePeriod, Qty, ProdID) select 20080705,660,2; 
insert into #s (TimePeriod, Qty, ProdID) select 20080706,370,2; 
insert into #s (TimePeriod, Qty, ProdID) select 20080707,574,2; 
insert into #s (TimePeriod, Qty, ProdID) select 20080705,764,3; 
insert into #s (TimePeriod, Qty, ProdID) select 20080706,294,3; 
insert into #s (TimePeriod, Qty, ProdID) select 20080707,202,3; 
insert into #s (TimePeriod, Qty, ProdID) select 20080705,59,4; 
insert into #s (TimePeriod, Qty, ProdID) select 20080706,247,4; 
insert into #s (TimePeriod, Qty, ProdID) select 20080707,731,4; 


DECLARE @cmd NVARCHAR(max); 
SET @cmd = 'SELECT TimePeriod ' +
( SELECT ' , SUM(['+CONVERT(NVARCHAR,ProdID)+']) AS ['+ ProdName +']'
  FROM #p AS p
  FOR XML PATH('')
)
+
' FROM( 
	SELECT #s.TimePeriod, #s.Qty, #p.ProdID, #p.ProdName 
	FROM #p 
	INNER JOIN #s on #p.ProdID = #s.ProdID 
  ) p 
PIVOT ( 
	SUM( Qty ) 
	FOR ProdID IN 
	( [0] '+ 
( SELECT ' , ['+CONVERT(NVARCHAR,ProdID)+']' 
  FROM #p AS p 
  FOR XML PATH('') 
) 
+ 
'   ) 
  ) AS pvt 
GROUP BY TimePeriod 
ORDER BY TimePeriod ; ' ; 

PRINT @cmd; 
EXEC(@cmd); 

DROP TABLE #p; 
DROP TABLE #s; 

GeneralRe: Create a table with columns' name as primary columns data Pin
prithaa8-Jul-08 21:02
prithaa8-Jul-08 21:02 
AnswerRe: Create a table with columns' name as primary columns data [modified] Pin
leoinfo9-Jul-08 3:06
leoinfo9-Jul-08 3:06 
QuestionHow to simplify the query Pin
BalasubramanianK7-Jul-08 1:01
BalasubramanianK7-Jul-08 1:01 
AnswerRe: How to simplify the query Pin
ChandraRam7-Jul-08 3:09
ChandraRam7-Jul-08 3:09 
Questionhow to write "connection string" in namespace or webconfig file ? Pin
sacr837-Jul-08 0:29
sacr837-Jul-08 0:29 
AnswerCross post Pin
dan!sh 7-Jul-08 0:37
professional dan!sh 7-Jul-08 0:37 
Questiondifference between sql server 2000 and 2005, Pin
Shaik Haneef7-Jul-08 0:06
Shaik Haneef7-Jul-08 0:06 
Questionhow to find string match in any updated columns in a trigger Pin
P. S. Pundeer6-Jul-08 19:18
P. S. Pundeer6-Jul-08 19:18 
AnswerRe: how to find string match in any updated columns in a trigger Pin
Giorgi Dalakishvili6-Jul-08 22:00
mentorGiorgi Dalakishvili6-Jul-08 22:00 
QuestionOptimizing query [modified] Pin
Puneri6-Jul-08 2:43
Puneri6-Jul-08 2:43 
QuestionRe: Optimizing query Pin
Puneri6-Jul-08 3:34
Puneri6-Jul-08 3:34 
AnswerRe: Optimizing query Pin
Kjetil Svendsen6-Jul-08 21:34
Kjetil Svendsen6-Jul-08 21:34 
Questionscript Pin
rezarafiee6-Jul-08 2:15
rezarafiee6-Jul-08 2:15 
AnswerRe: script Pin
Saksida Bojan6-Jul-08 3:26
Saksida Bojan6-Jul-08 3:26 
AnswerRe: script Pin
Pete O'Hanlon6-Jul-08 9:14
mvePete O'Hanlon6-Jul-08 9:14 
QuestionEmail Alert after Insert on table Pin
obarahmeh5-Jul-08 21:42
obarahmeh5-Jul-08 21:42 
AnswerRe: Email Alert after Insert on table Pin
N a v a n e e t h6-Jul-08 4:15
N a v a n e e t h6-Jul-08 4:15 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.