SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 view without hardcoding
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

gagani
Yak Posting Veteran

94 Posts

Posted - 03/11/2013 :  07:22:58  Show Profile  Reply with Quote
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) as
C, 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 H
from orders o left join
(select orderid,sum(quantity) as quantity from order_productname
where product = 'A'
group by orderid )a
on o.orderid = a.orderid left join
(select orderid,sum(quantity) as quantity from order_productname
where product = 'B'
group by orderid) b
on o.orderid = b.orderid left join
(select orderid,sum(quantity) as quantity from order_productname
where product = 'C'
group by orderid)c
on o.orderid = c.orderid left join
(select orderid,sum(quantity) as quantity from order_productname
where product = 'D'
group by orderid) d
on o.orderid = d.orderid left join
(select orderid, sum(quantity) as quantity from order_productname
where product = 'E'
group by orderid) e
on o.orderid = e.orderid left join
(select orderid,sum(quantity) as quantity from order_productname
where product = 'F'
group by orderid)f
on o.orderid = f.orderid left join
(select orderid,sum(quantity) as quantity from order_productname
where product = 'G'
group by orderid)g
on o.orderid = g.orderid left join
(select orderid, sum(quantity) as quantity from order_productname
where product = 'H'
group by orderid)h
on o.orderid = h.orderid

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 03/11/2013 :  07:26:35  Show Profile  Reply with Quote
see

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

gagani
Yak Posting Veteran

94 Posts

Posted - 03/11/2013 :  12:19:59  Show Profile  Reply with Quote
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 like

select *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?

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 03/11/2013 :  12:23:12  Show Profile  Reply with Quote
use INSERT ...EXEC

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

gagani
Yak Posting Veteran

94 Posts

Posted - 03/11/2013 :  12:39:18  Show Profile  Reply with Quote
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

Edited by - gagani on 03/11/2013 12:40:25
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 03/11/2013 :  12:41:37  Show Profile  Reply with Quote
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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

gagani
Yak Posting Veteran

94 Posts

Posted - 03/11/2013 :  15:45:59  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 03/12/2013 :  00:35:51  Show Profile  Reply with Quote
do something like

declare @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 columns
EXEC(@CreateTableQuery)


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

gagani
Yak Posting Veteran

94 Posts

Posted - 03/12/2013 :  04:44:01  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 03/12/2013 :  05:11:40  Show Profile  Reply with Quote
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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

gagani
Yak Posting Veteran

94 Posts

Posted - 03/12/2013 :  06:20:04  Show Profile  Reply with Quote
Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'FOR'.

I think XML PATH is not supported in 2000. any replacement for it
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 03/12/2013 :  06:46:31  Show Profile  Reply with Quote
you need to use FOR XML RAW or FOR XML AUTO in 2000

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2168 Posts

Posted - 03/12/2013 :  06:55:59  Show Profile  Reply with Quote

try this ........
SELECT @columnlist = @columnlist + ','+ product
FROM (SELECT DISTINCT product FROM order_productname ORDER BY product)
ORDER BY product
SET @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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 03/12/2013 :  07:55:14  Show Profile  Reply with Quote
quote:
Originally posted by bandi


try this ........
SELECT @columnlist = @columnlist + ','+ product
FROM (SELECT DISTINCT product FROM order_productname ORDER BY product)
ORDER BY product
SET @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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2168 Posts

Posted - 03/12/2013 :  08:04:23  Show Profile  Reply with Quote
Yes Visakh......

SELECT @columnlist = @columnlist + ','+ product
FROM (SELECT DISTINCT product FROM order_productname ORDER BY product)
ORDER BY product
SET @columnlist = STUFF( @columnlist , 1, 1 '')

I have provided alternate solution instead of that part......



--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 03/12/2013 :  13:05:10  Show Profile  Reply with Quote
quote:
Originally posted by bandi

Yes Visakh......

SELECT @columnlist = @columnlist + ','+ product
FROM (SELECT DISTINCT product FROM order_productname ORDER BY product)
ORDER BY product
SET @columnlist = STUFF( @columnlist , 1, 1 '')

I have provided alternate solution instead of that part......



--
Chandu


wont work unless @ColumnList is initialized before

otherwise do this


SELECT @columnlist = COALESCE(@columnlist,'') + ','+ product
FROM (SELECT DISTINCT product FROM order_productname ORDER BY product)
ORDER BY product
SET @columnlist = STUFF( @columnlist , 1, 1 '')


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000