Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 view without hardcoding

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) 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

52326 Posts

Posted - 2013-03-11 : 07:26:35
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
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 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

52326 Posts

Posted - 2013-03-11 : 12:23:12
use INSERT ...EXEC

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

Go to Top of Page

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
Go to Top of Page

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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-12 : 00:35:51
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
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
Go to Top of Page

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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

gagani
Posting Yak Master

112 Posts

Posted - 2013-03-12 : 06:20:04
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

52326 Posts

Posted - 2013-03-12 : 06:46:31
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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-12 : 06:55:59

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

52326 Posts

Posted - 2013-03-12 : 07:55:14
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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-12 : 08:04:23
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

52326 Posts

Posted - 2013-03-12 : 13:05:10
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
   

- Advertisement -