view - SQL Server Forums

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

Author

Topic

gagani
Posting Yak Master

112 Posts

Posted - 2012-03-20 : 05:13:37

For the given table,

orderid product quantity
501 A 1
501 B 3
501 C 1

501 D 1

123 B 1

can anyone create the view as follows

orderid product quantity

501 A-B-C-D 6

123 B 1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-20 : 14:51:06

[code]
CREATE VIEW viewname
AS
SELECT orderid,
STUFF((SELECT '-' + product FROM table WHERE orderid = t. orderid ORDER BY product FOR XML PATH('')),1,1,'') AS product,
quantity
FROM (SELECT orderid,SUM(quantity) AS quantity
FROM table
GROUP BY orderid)t
[/code]

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

gagani
Posting Yak Master

112 Posts

Posted - 2012-03-20 : 15:48:45

sorry to take your time.

there are couple of errors in it. I am new to this correct it.
could you please look into these errors
incorrect syntax near the keyword 'FOR'
incorrect syntax near 't'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-20 : 15:52:31

there was an additional space character
also i hope you're using sql 2005 and above


CREATE VIEW viewname
AS
SELECT orderid,
       STUFF((SELECT '-' + product FROM table WHERE orderid = t.orderid ORDER BY product FOR XML PATH('')),1,1,'') AS product,
quantity
FROM (SELECT orderid,SUM(quantity) AS quantity 
      FROM table
      GROUP BY orderid)t

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

gagani
Posting Yak Master

112 Posts

Posted - 2012-03-20 : 15:57:09

its sql server 2000

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-20 : 15:59:52

then it wont work. FOR XML PATH is only from sql 2005 and above.

in 2000, you've to use udf for that

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

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-20 : 18:57:58

Can you describe WHY you need this?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources