Combining results in one string

I have a table as follows:
SizeID | Description
1 Extra Small
2 Small
3 Medium
4 Large
5 Extra Large
And then a Product_Size relationship table:
ProductID | SizeID
1000 1
1001 3
1001 4
1001 5
1010 2
1010 3
I want to be able to write a query which returns the size descriptions in
one single string, separated by commas.
For example, if I request for Product #1001, I want the return string to be:
'Medium, Large, Extra Large'
Please show me how this query can be written.
Thanks!You can do this by creating function.
CREATE FUNCTION dbo.GetSize(@.ProductId int)
RETURNS varchar(50)
AS
Begin
Declare @.ReturnValue Varchar(500), @.descripton Varchar(50)
Set @.ReturnValue = ''
Declare ProductSize Cursor
For Select Description
From Product_Size, SizeMaster
Where Product_Size.SizeID = SizeMaster .SizeID
AND ProductId = @.ProductId
Open ProductSize
Fetch Next From ProductSize into @.descripton
While @.@.Fetch_status = 0
Begin
SET @.ReturnValue = @.ReturnValue + @.descripton + ','
Fetch Next From ProductSize into @.descripton
End
Set @.ReturnValue = left(@.ReturnValue,Len(@.ReturnValue)-1)
Close ProductSize
Deallocate ProductSize
Return(@.ReturnValue)
End
Select Distinct ProductId, dbo.GetSize(productID) from Product_Size
Thanks
Baiju
"Uncle Ben" <spamfree@.nospam.com> wrote in message
news:OO1L1kGJFHA.2356@.TK2MSFTNGP12.phx.gbl...
> I have a table as follows:
> SizeID | Description
> 1 Extra Small
> 2 Small
> 3 Medium
> 4 Large
> 5 Extra Large
> And then a Product_Size relationship table:
> ProductID | SizeID
> 1000 1
> 1001 3
> 1001 4
> 1001 5
> 1010 2
> 1010 3
> I want to be able to write a query which returns the size descriptions in
> one single string, separated by commas.
> For example, if I request for Product #1001, I want the return string to
be:
> 'Medium, Large, Extra Large'
> Please show me how this query can be written.
> Thanks!
>|||I made a simillar request yesterday. check the post titled "Concating Values
of a Column based on a Group"
Gopi
"Uncle Ben" <spamfree@.nospam.com> wrote in message
news:OO1L1kGJFHA.2356@.TK2MSFTNGP12.phx.gbl...
>I have a table as follows:
> SizeID | Description
> 1 Extra Small
> 2 Small
> 3 Medium
> 4 Large
> 5 Extra Large
> And then a Product_Size relationship table:
> ProductID | SizeID
> 1000 1
> 1001 3
> 1001 4
> 1001 5
> 1010 2
> 1010 3
> I want to be able to write a query which returns the size descriptions in
> one single string, separated by commas.
> For example, if I request for Product #1001, I want the return string to
> be:
> 'Medium, Large, Extra Large'
> Please show me how this query can be written.
> Thanks!
>

Combining 3 SQL statements

Hey all. Ive got a big problem with an sql statement Im working on.

There are 2 tables with a master/detail relationship. The Header Table
is the master, the Line Table is the detail. So for each Header, there
are many Lines, but a Line can only reference one Header.
There is a Line Total and Line Cost in each Line Record. Each Line
Record has a type.
What I want to be able to do is, for each Header, I want to Sum each
corresponding Line's Total and Cost where the type is either one value
or another. If the type is, for example, 10, only sum the Total, if its
type 2, only sum the Cost.

Therefore, after the query is executed, you should have a result set
something like this

Job : Job1 (header id)
Desc : Job0001 (header desc)
Cost : (sum of Line Costs where Line Type is 2 and header id is Job1)
Total : (sum of Line Totals where Line Type is 10 and header id is
Job1)
--------------------------------
Job : Job2 (header id)
Desc : Job0002 (header desc)
Cost : (sum of Line Costs where Line Type is 2 and header id is Job2)
Total : (sum of Line Totals where Line Type is 10 and header id is
Job2)
--------------------------------

etc.

Hope this makes sense. ThanksTry this one here:

Select
header_id,
header_desc,
SUM(CASE Line_type WHEN 2 THEN costs else 0 END),
SUM(CASE Line_type WHEN 10 THEN Totals else 0 END)
FROM headers
INNER JOIN
line
ON line.header_id = header.header_id

HTH, jens Suessmeyer.|||Jens (Jens@.sqlserver2005.de) writes:

> Try this one here:
> Select
> header_id,
> header_desc,
> SUM(CASE Line_type WHEN 2 THEN costs else 0 END),
> SUM(CASE Line_type WHEN 10 THEN Totals else 0 END)
> FROM headers
> INNER JOIN
> line
> ON line.header_id = header.header_id

Better:

SELECT h.header_id, h.header_desc,
SUM(CASE Line_type WHEN 2 THEN costs else 0 END),
SUM(CASE Line_type WHEN 10 THEN Totals else 0 END)
FROM headers h
JOIN line ON l.header_id = h.header_id
GROUP BY h.header_id, h.header_desc

Particularly that GROUP BY clause is quite important...

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||You are right, thats not even better that was missing in my example.|||Is there a way to say

SUM(CASE Line_type WHEN 10 THEN costs else 0 END),
SUM(CASE Line_type WHEN NOT 10 THEN Totals else 0 END) ?|||Its OK, I figured it out.
I ended up using

SUM(CASE WHEN Line_type = 10 THEN costs else 0 END),

SUM(CASE WHEN Line_type <> 10 THEN Totals else 0 END)

THanks for the help guys :)

Combined Primary Key - Why?

In a many to many relationship, say Product to Orders with ProductOrders
being the associative entity, what is the most commonly used definition for
Primary keys (PK) in the associative entity and why?
a)ProductOrders: ProductID - PK, OrderID - PK
OR
b) ProductOrders: ProductOrderID(PK, mostly identity), ProductID (not PK),
OrderID not (PK).
If (b), then how do I ensure that a combination of ProductID and OrderID are
unique?
Thanks,
Naveen>> If (b), then how do I ensure that a combination of ProductID and OrderID
In t-SQL, you'd use a NOT NULL UNIQUE CONSTRAINT on those values.
Anith|||I would normally go with ProductID, OrderID for primary key, as that is the
natural key. If I have to go with a surrogate key, then I'd make sure
there's a unique index/constraint on ProductID, OrderID combination.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Naveen" <Naveen@.discussions.microsoft.com> wrote in message
news:C88C1636-C31F-4C63-8DAB-248C4D4C10B3@.microsoft.com...
In a many to many relationship, say Product to Orders with ProductOrders
being the associative entity, what is the most commonly used definition for
Primary keys (PK) in the associative entity and why?
a)ProductOrders: ProductID - PK, OrderID - PK
OR
b) ProductOrders: ProductOrderID(PK, mostly identity), ProductID (not PK),
OrderID not (PK).
If (b), then how do I ensure that a combination of ProductID and OrderID are
unique?
Thanks,
Naveen|||You can create a UNIQUE constraint on the combination of ProductID and
OrderID. Although my preference is to have a Primary Key on ProductID and
OrderID, and, if I think a single column (Identity) key is useful, to have
the UNIQUE constraint on the Identity column. In this scenario there is
usually not much need to have an Identity column on the ProductOrders table
though.
Jacco Schalkwijk
SQL Server MVP
"Naveen" <Naveen@.discussions.microsoft.com> wrote in message
news:C88C1636-C31F-4C63-8DAB-248C4D4C10B3@.microsoft.com...
> In a many to many relationship, say Product to Orders with ProductOrders
> being the associative entity, what is the most commonly used definition
> for
> Primary keys (PK) in the associative entity and why?
> a)ProductOrders: ProductID - PK, OrderID - PK
> OR
> b) ProductOrders: ProductOrderID(PK, mostly identity), ProductID (not PK),
> OrderID not (PK).
> If (b), then how do I ensure that a combination of ProductID and OrderID
> are
> unique?
> Thanks,
> Naveen|||"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:OjnP5cEPFHA.3388@.TK2MSFTNGP10.phx.gbl...
>I would normally go with ProductID, OrderID for primary key, as that is the
> natural key. If I have to go with a surrogate key, then I'd make sure
> there's a unique index/constraint on ProductID, OrderID combination.
> --
Me to. Although I would pick (OrderId, ProductID), if order-wise access is
a often a more common access path than product-wise access. Whichever
column leads your clustered index will have the cheapest access path.
And put a non-clustered index on whichever column is not the leading column
in the clustered index (OrderID). Foreign keys should normally be supported
by an index.
create table ProductOrders
(
ProductID int not null references Products,
OrderID int not null refereneces Orders on delete cascade,
constraint pk_ProductOrders primary key(OrderID,ProductID)
)
create index ix_ProductOrdersProduct on ProductOrders(ProductId)
David|||I wasn't talking about the order of columns within the compound key - but
yes, that's a good point.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23NHEglEPFHA.2520@.tk2msftngp13.phx.gbl...
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:OjnP5cEPFHA.3388@.TK2MSFTNGP10.phx.gbl...
>I would normally go with ProductID, OrderID for primary key, as that is the
> natural key. If I have to go with a surrogate key, then I'd make sure
> there's a unique index/constraint on ProductID, OrderID combination.
> --
Me to. Although I would pick (OrderId, ProductID), if order-wise access is
a often a more common access path than product-wise access. Whichever
column leads your clustered index will have the cheapest access path.
And put a non-clustered index on whichever column is not the leading column
in the clustered index (OrderID). Foreign keys should normally be supported
by an index.
create table ProductOrders
(
ProductID int not null references Products,
OrderID int not null refereneces Orders on delete cascade,
constraint pk_ProductOrders primary key(OrderID,ProductID)
)
create index ix_ProductOrdersProduct on ProductOrders(ProductId)
David|||There is only one key, (order_id, product_id) by definition. This is
basic RDBMS; a key must be made up of attributes that exist in the data
model. There is no ProductOrderID(mostly identity) in the real world;
it is derived from the internal state of the hardware at insertion
time:
CREATE TABLE Purchases
(product_id INTEGER NOT NULL
REFERENCES Inventory(product_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
order_id INTEGER NOT NULL
REFERENCES Orders(order_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
PRIMARY KEY (order_id, product_id));
Newbies screw up and do it the other way. They can then get duplicates
and destroy the data integrity of the schema.|||This Product Orders table is just a OrderDetails, OrderItems or
InvoiceDetails table..
If you are going have any other tables "hanging off" this table, where you
will need to have a FK referring back to the ProductOrders (I'd call it
OrderDetails)
Then I'd go with b) strictly to avoid having to use composite FKs in other
table(s).
In that event, add another "Alternate" key (using Unique Index, or Unique
Constraint, on (OrderID, ProductID) to ensure uniqueness.
Regardless of whether you also have a single column surrogate key, make the
Composite key the Clustered Index, and if more queries will select data by
OrderID than by ProductID from this table, (THis is normally true for such
tables) use (OrderID, ProductID) as the sequence. But also put another inde
x
on ProductID by itself, for Joins back to Product Table
"Naveen" wrote:

> In a many to many relationship, say Product to Orders with ProductOrders
> being the associative entity, what is the most commonly used definition fo
r
> Primary keys (PK) in the associative entity and why?
> a)ProductOrders: ProductID - PK, OrderID - PK
> OR
> b) ProductOrders: ProductOrderID(PK, mostly identity), ProductID (not PK),
> OrderID not (PK).
> If (b), then how do I ensure that a combination of ProductID and OrderID a
re
> unique?
> Thanks,
> Naveensqlsql

Combine multiple results of subquery

Table users:
userid, name, added...

Table groups
groupid, groupname...

Table groupadmins:
userid, groupid

The users to groups relationship is many-to-many, which is why I created the intermediate table. I would like to return a recordset like:
userid, name, groupids
12344, 'Bob', '123,234,345'

If I try to just select groupid from groupadmins:
select userid, name, (select groupid from groupadmins where groupadmins.userid = users.userid) as groupids from users

then I'll get an error that a subquery is returning multiple results. Some users are not group admins and those that are may have a single or multiple groups. The only thing I can think of is to select the groupids seperately and use a cursor to loop through the results and build a string with the groupids. Then I would select the string with the rest of the fields that I want for return. Is there a better way to do this?

The best example of how to do this in this article by Aaron Bertrand:

http://databases.aspfaq.com/general/how-do-i-concatenate-strings-from-a-column-into-a-single-row.html

If you are using SQL Server 2005, the FOR XML PATTH solution is really nice.

|||

Thanks a bunch. I tried googling and searching these forums, but without the right keywords, useful results can be hard to come by.

aspfaq.com is now bookmarked for many future references.

sqlsql