Author |
Topic |
gagani
Posting Yak Master
112 Posts |
Posted - 2013-03-11 : 07:22:58
|
The following is the view which is hardcoded. But there is a chance of increasing the number of products in the future. Could anyone create the view which can fit more number of products in the future without hardcoding. Can anyone recreate the view without hardcoding?select o.orderid, isnull(a.quantity,0) as A,isnull(b.quantity,0) as B, isnull(c.quantity,0) asC, isnull(d.quantity,0) as D, isnull(e.quantity,0)as E, isnull(f.quantity,0)as F, isnull(g.quantity,0) as G, isnull(h.quantity,0) as Hfrom orders o left join(select orderid,sum(quantity) as quantity from order_productnamewhere product = 'A'group by orderid )aon o.orderid = a.orderid left join(select orderid,sum(quantity) as quantity from order_productnamewhere product = 'B'group by orderid) bon o.orderid = b.orderid left join(select orderid,sum(quantity) as quantity from order_productnamewhere product = 'C'group by orderid)con o.orderid = c.orderid left join(select orderid,sum(quantity) as quantity from order_productnamewhere product = 'D'group by orderid) don o.orderid = d.orderid left join(select orderid, sum(quantity) as quantity from order_productnamewhere product = 'E'group by orderid) eon o.orderid = e.orderid left join(select orderid,sum(quantity) as quantity from order_productnamewhere product = 'F'group by orderid)fon o.orderid = f.orderid left join(select orderid,sum(quantity) as quantity from order_productnamewhere product = 'G'group by orderid)gon o.orderid = g.orderid left join(select orderid, sum(quantity) as quantity from order_productnamewhere product = 'H'group by orderid)hon o.orderid = h.orderid |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
gagani
Posting Yak Master
112 Posts |
Posted - 2013-03-11 : 12:19:59
|
Thanks for that.The following is working fine.EXEC CrossTab'select orderid, product, quantity from order_productname','product','sum(quantity)[]','orderid'How to copy the results of stored procedure into a table?something likeselect *into table1 from (EXEC CrossTab'select orderid, product, quantity from order_productname','product','sum(quantity)[]','orderid' )I want to use the results of the stored procedures and join with some other tables in other queries. can anyone help please? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-11 : 12:23:12
|
use INSERT ...EXEC------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
gagani
Posting Yak Master
112 Posts |
Posted - 2013-03-11 : 12:39:18
|
insert into table should be defined in the same structure as returned by stored procedure. but the stored procedure results are dynamic- I mean there could be increase of columns eventually.I need a query to create a view or table to use elsewhere which stores the results by querying the stored procedure |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-11 : 12:41:37
|
you need to create table also dynamically in that case. store the pivot column list in a variable and use that in CREATE TABLE statement dynamically to add those many columns. then use INSERT..EXEC------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
gagani
Posting Yak Master
112 Posts |
Posted - 2013-03-11 : 15:45:59
|
could you please let me know how to create a table dynamically by storing the pivot column list in a variable. I am not getting quite right with it. sorry to bother |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-12 : 00:35:51
|
do something likedeclare @columnlist varchar(8000)SELECT @columnlist=(SELECT STUFF((SELECT DISTINCT ',' + product FROM order_productname ORDER BY ',' + product FOR XML PATH('')),1,1,''))DECLARE @CreateTableQuery varchar(5000)SET @CreateTableQuery = 'CREATE TABLE Tablename (Column1 int,Column2 varchar(10)... other static columns here, ' + REPLACE(@columnlist,',',' varchar(100),') + ' varchar(100));'--this will create the table with dynamic columnsEXEC(@CreateTableQuery) ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
gagani
Posting Yak Master
112 Posts |
Posted - 2013-03-12 : 04:44:01
|
i have created a table named table1 and inserted the results of stored procedure into that table1. the results of stored procedure may change or it may get increased with another column.how does table1 get increased with a new column when there is sp results has a new column. table1 is static after It's creation. i want table1 to join with some other tables in a query.table1 should populate itself with the results of sp and should be able to increase the columns based in the results of sp |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-12 : 05:11:40
|
The table will get created based on sp output each time, then you do required join etc to get your output and then finally drop it after each use.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
gagani
Posting Yak Master
112 Posts |
Posted - 2013-03-12 : 06:20:04
|
Msg 156, Level 15, State 1, Line 5Incorrect syntax near the keyword 'FOR'.I think XML PATH is not supported in 2000. any replacement for it |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-12 : 06:46:31
|
you need to use FOR XML RAW or FOR XML AUTO in 2000------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-03-12 : 06:55:59
|
try this ........SELECT @columnlist = @columnlist + ','+ productFROM (SELECT DISTINCT product FROM order_productname ORDER BY product)ORDER BY productSET @columnlist = STUFF( @columnlist , 1, 1 '')instead of SELECT @columnlist=(SELECT STUFF((SELECT DISTINCT ',' + product FROM order_productname ORDER BY ',' + product FOR XML PATH('')),1,1,''))--Chandu |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-12 : 07:55:14
|
quote: Originally posted by bandi try this ........SELECT @columnlist = @columnlist + ','+ productFROM (SELECT DISTINCT product FROM order_productname ORDER BY product)ORDER BY productSET @columnlist = STUFF( @columnlist , 1, 1 '')instead of SELECT @columnlist=(SELECT STUFF((SELECT DISTINCT ',' + product FROM order_productname ORDER BY ',' + product FOR XML PATH('')),1,1,''))--Chandu
FOR XML PATH is not supported in SQL 2000------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-03-12 : 08:04:23
|
Yes Visakh......SELECT @columnlist = @columnlist + ','+ productFROM (SELECT DISTINCT product FROM order_productname ORDER BY product)ORDER BY productSET @columnlist = STUFF( @columnlist , 1, 1 '')I have provided alternate solution instead of that part......--Chandu |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-12 : 13:05:10
|
quote: Originally posted by bandi Yes Visakh......SELECT @columnlist = @columnlist + ','+ productFROM (SELECT DISTINCT product FROM order_productname ORDER BY product)ORDER BY productSET @columnlist = STUFF( @columnlist , 1, 1 '')I have provided alternate solution instead of that part......--Chandu
wont work unless @ColumnList is initialized beforeotherwise do thisSELECT @columnlist = COALESCE(@columnlist,'') + ','+ productFROM (SELECT DISTINCT product FROM order_productname ORDER BY product)ORDER BY productSET @columnlist = STUFF( @columnlist , 1, 1 '') ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|