[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

Age Old Concatenating rows question - Small details left..

Mike D.

3/19/2007 10:53:00 PM

Hi All,

I know the 'Concatenating row values' question has been asked a million
times, however I have not seen how to make a slight adjustment to the
results.

If I run the query below on Northwind, rows 6 and 7 have trailing
delimiters. Usually I would handle this in the asp.net code but I'm working
with a OR mapping tool that is really 'black box' to me. I need to get
everything encapsulated in a SQL view and all will be well.

Please show me what SQL function can handle this!

SELECT CategoryId,
MAX( CASE seq WHEN 1 THEN ProductName ELSE '' END ) + ' / ' +
MAX( CASE seq WHEN 2 THEN ProductName ELSE '' END ) + ' / ' +
MAX( CASE seq WHEN 3 THEN ProductName ELSE '' END ) + ' / ' +
MAX( CASE seq WHEN 4 THEN ProductName ELSE '' END ) + ' / ' +
MAX( CASE seq WHEN 5 THEN ProductName ELSE '' END ) + ' / ' +
MAX( CASE seq WHEN 6 THEN ProductName ELSE '' END ) + ' / ' +
MAX( CASE seq WHEN 7 THEN ProductName ELSE '' END )
FROM ( SELECT p1.CategoryId, p1.ProductName,
( SELECT COUNT(*)
FROM Northwind.dbo.Products p2
WHERE p2.CategoryId = p1.CategoryId
AND p2.ProductName <= p1.ProductName )
FROM Northwind.dbo.Products p1
) D ( CategoryId, ProductName, seq )
GROUP BY CategoryId

TIA!
Mike D.


4 Answers

Tom Cooper

3/19/2007 11:09:00 PM

0

How about

SELECT CategoryId,
MAX( CASE seq WHEN 1 THEN ProductName ELSE '' END ) +
MAX( CASE seq WHEN 2 THEN ' / ' + ProductName ELSE '' END ) +
MAX( CASE seq WHEN 3 THEN ' / ' + ProductName ELSE '' END ) +
MAX( CASE seq WHEN 4 THEN ' / ' + ProductName ELSE '' END ) +
MAX( CASE seq WHEN 5 THEN ' / ' + ProductName ELSE '' END ) +
MAX( CASE seq WHEN 6 THEN ' / ' + ProductName ELSE '' END ) +
MAX( CASE seq WHEN 7 THEN ' / ' + ProductName ELSE '' END )
FROM ( SELECT p1.CategoryId, p1.ProductName,
( SELECT COUNT(*)
FROM Northwind.dbo.Products p2
WHERE p2.CategoryId = p1.CategoryId
AND p2.ProductName <= p1.ProductName )
FROM Northwind.dbo.Products p1
) D ( CategoryId, ProductName, seq )
GROUP BY CategoryId

Tom

"Mike D." <nospammagepofavor@ohyeah.com> wrote in message
news:eE8UglnaHHA.4000@TK2MSFTNGP02.phx.gbl...
> Hi All,
>
> I know the 'Concatenating row values' question has been asked a million
> times, however I have not seen how to make a slight adjustment to the
> results.
>
> If I run the query below on Northwind, rows 6 and 7 have trailing
> delimiters. Usually I would handle this in the asp.net code but I'm
> working with a OR mapping tool that is really 'black box' to me. I need
> to get everything encapsulated in a SQL view and all will be well.
>
> Please show me what SQL function can handle this!
>
> SELECT CategoryId,
> MAX( CASE seq WHEN 1 THEN ProductName ELSE '' END ) + ' / ' +
> MAX( CASE seq WHEN 2 THEN ProductName ELSE '' END ) + ' / ' +
> MAX( CASE seq WHEN 3 THEN ProductName ELSE '' END ) + ' / ' +
> MAX( CASE seq WHEN 4 THEN ProductName ELSE '' END ) + ' / ' +
> MAX( CASE seq WHEN 5 THEN ProductName ELSE '' END ) + ' / ' +
> MAX( CASE seq WHEN 6 THEN ProductName ELSE '' END ) + ' / ' +
> MAX( CASE seq WHEN 7 THEN ProductName ELSE '' END )
> FROM ( SELECT p1.CategoryId, p1.ProductName,
> ( SELECT COUNT(*)
> FROM Northwind.dbo.Products p2
> WHERE p2.CategoryId = p1.CategoryId
> AND p2.ProductName <= p1.ProductName )
> FROM Northwind.dbo.Products p1
> ) D ( CategoryId, ProductName, seq )
> GROUP BY CategoryId
>
> TIA!
> Mike D.
>


Alejandro Mesa

3/19/2007 11:09:00 PM

0

Try:

SELECT CategoryId,
MAX( CASE seq WHEN 1 THEN ProductName ELSE '' END ) +
MAX( CASE seq WHEN 2 THEN ' / ' + ProductName ELSE '' END ) +
MAX( CASE seq WHEN 3 THEN ' / ' + ProductName ELSE '' END ) +
MAX( CASE seq WHEN 4 THEN ' / ' + ProductName ELSE '' END ) +
MAX( CASE seq WHEN 5 THEN ' / ' + ProductName ELSE '' END ) +
MAX( CASE seq WHEN 6 THEN ' / ' + ProductName ELSE '' END ) +
MAX( CASE seq WHEN 7 THEN ' / ' + ProductName ELSE '' END )
FROM ( SELECT p1.CategoryId, p1.ProductName,
( SELECT COUNT(*)
FROM Northwind.dbo.Products p2
WHERE p2.CategoryId = p1.CategoryId
AND p2.ProductName <= p1.ProductName )
FROM Northwind.dbo.Products p1
) D ( CategoryId, ProductName, seq )
GROUP BY CategoryId
go


AMB


"Mike D." wrote:

> Hi All,
>
> I know the 'Concatenating row values' question has been asked a million
> times, however I have not seen how to make a slight adjustment to the
> results.
>
> If I run the query below on Northwind, rows 6 and 7 have trailing
> delimiters. Usually I would handle this in the asp.net code but I'm working
> with a OR mapping tool that is really 'black box' to me. I need to get
> everything encapsulated in a SQL view and all will be well.
>
> Please show me what SQL function can handle this!
>
> SELECT CategoryId,
> MAX( CASE seq WHEN 1 THEN ProductName ELSE '' END ) + ' / ' +
> MAX( CASE seq WHEN 2 THEN ProductName ELSE '' END ) + ' / ' +
> MAX( CASE seq WHEN 3 THEN ProductName ELSE '' END ) + ' / ' +
> MAX( CASE seq WHEN 4 THEN ProductName ELSE '' END ) + ' / ' +
> MAX( CASE seq WHEN 5 THEN ProductName ELSE '' END ) + ' / ' +
> MAX( CASE seq WHEN 6 THEN ProductName ELSE '' END ) + ' / ' +
> MAX( CASE seq WHEN 7 THEN ProductName ELSE '' END )
> FROM ( SELECT p1.CategoryId, p1.ProductName,
> ( SELECT COUNT(*)
> FROM Northwind.dbo.Products p2
> WHERE p2.CategoryId = p1.CategoryId
> AND p2.ProductName <= p1.ProductName )
> FROM Northwind.dbo.Products p1
> ) D ( CategoryId, ProductName, seq )
> GROUP BY CategoryId
>
> TIA!
> Mike D.
>
>
>

Mike D.

3/19/2007 11:19:00 PM

0

Thanks guys... that works perfect!

One other question if I may.. How can the rest of the Products table be
added to this query?


"Alejandro Mesa" <AlejandroMesa@discussions.microsoft.com> wrote in message
news:AC075181-F4C6-4CB0-8EAD-C368A1CF8D30@microsoft.com...
> Try:
>
> SELECT CategoryId,
> MAX( CASE seq WHEN 1 THEN ProductName ELSE '' END ) +
> MAX( CASE seq WHEN 2 THEN ' / ' + ProductName ELSE '' END ) +
> MAX( CASE seq WHEN 3 THEN ' / ' + ProductName ELSE '' END ) +
> MAX( CASE seq WHEN 4 THEN ' / ' + ProductName ELSE '' END ) +
> MAX( CASE seq WHEN 5 THEN ' / ' + ProductName ELSE '' END ) +
> MAX( CASE seq WHEN 6 THEN ' / ' + ProductName ELSE '' END ) +
> MAX( CASE seq WHEN 7 THEN ' / ' + ProductName ELSE '' END )
> FROM ( SELECT p1.CategoryId, p1.ProductName,
> ( SELECT COUNT(*)
> FROM Northwind.dbo.Products p2
> WHERE p2.CategoryId = p1.CategoryId
> AND p2.ProductName <= p1.ProductName )
> FROM Northwind.dbo.Products p1
> ) D ( CategoryId, ProductName, seq )
> GROUP BY CategoryId
> go
>
>
> AMB
>
>
> "Mike D." wrote:
>
>> Hi All,
>>
>> I know the 'Concatenating row values' question has been asked a million
>> times, however I have not seen how to make a slight adjustment to the
>> results.
>>
>> If I run the query below on Northwind, rows 6 and 7 have trailing
>> delimiters. Usually I would handle this in the asp.net code but I'm
>> working
>> with a OR mapping tool that is really 'black box' to me. I need to get
>> everything encapsulated in a SQL view and all will be well.
>>
>> Please show me what SQL function can handle this!
>>
>> SELECT CategoryId,
>> MAX( CASE seq WHEN 1 THEN ProductName ELSE '' END ) + ' / ' +
>> MAX( CASE seq WHEN 2 THEN ProductName ELSE '' END ) + ' / ' +
>> MAX( CASE seq WHEN 3 THEN ProductName ELSE '' END ) + ' / ' +
>> MAX( CASE seq WHEN 4 THEN ProductName ELSE '' END ) + ' / ' +
>> MAX( CASE seq WHEN 5 THEN ProductName ELSE '' END ) + ' / ' +
>> MAX( CASE seq WHEN 6 THEN ProductName ELSE '' END ) + ' / ' +
>> MAX( CASE seq WHEN 7 THEN ProductName ELSE '' END )
>> FROM ( SELECT p1.CategoryId, p1.ProductName,
>> ( SELECT COUNT(*)
>> FROM Northwind.dbo.Products p2
>> WHERE p2.CategoryId = p1.CategoryId
>> AND p2.ProductName <= p1.ProductName )
>> FROM Northwind.dbo.Products p1
>> ) D ( CategoryId, ProductName, seq )
>> GROUP BY CategoryId
>>
>> TIA!
>> Mike D.
>>
>>
>>


Mike D.

3/19/2007 11:26:00 PM

0

Nevermind... my real database schema is going to require something
different. Looks like a dumb question for this table!


"Mike D." <nospammagepofavor@ohyeah.com> wrote in message
news:eMWJ4znaHHA.2064@TK2MSFTNGP05.phx.gbl...
> Thanks guys... that works perfect!
>
> One other question if I may.. How can the rest of the Products table be
> added to this query?
>
>
> "Alejandro Mesa" <AlejandroMesa@discussions.microsoft.com> wrote in
> message news:AC075181-F4C6-4CB0-8EAD-C368A1CF8D30@microsoft.com...
>> Try:
>>
>> SELECT CategoryId,
>> MAX( CASE seq WHEN 1 THEN ProductName ELSE '' END ) +
>> MAX( CASE seq WHEN 2 THEN ' / ' + ProductName ELSE '' END ) +
>> MAX( CASE seq WHEN 3 THEN ' / ' + ProductName ELSE '' END ) +
>> MAX( CASE seq WHEN 4 THEN ' / ' + ProductName ELSE '' END ) +
>> MAX( CASE seq WHEN 5 THEN ' / ' + ProductName ELSE '' END ) +
>> MAX( CASE seq WHEN 6 THEN ' / ' + ProductName ELSE '' END ) +
>> MAX( CASE seq WHEN 7 THEN ' / ' + ProductName ELSE '' END )
>> FROM ( SELECT p1.CategoryId, p1.ProductName,
>> ( SELECT COUNT(*)
>> FROM Northwind.dbo.Products p2
>> WHERE p2.CategoryId = p1.CategoryId
>> AND p2.ProductName <= p1.ProductName )
>> FROM Northwind.dbo.Products p1
>> ) D ( CategoryId, ProductName, seq )
>> GROUP BY CategoryId
>> go
>>
>>
>> AMB
>>
>>
>> "Mike D." wrote:
>>
>>> Hi All,
>>>
>>> I know the 'Concatenating row values' question has been asked a million
>>> times, however I have not seen how to make a slight adjustment to the
>>> results.
>>>
>>> If I run the query below on Northwind, rows 6 and 7 have trailing
>>> delimiters. Usually I would handle this in the asp.net code but I'm
>>> working
>>> with a OR mapping tool that is really 'black box' to me. I need to get
>>> everything encapsulated in a SQL view and all will be well.
>>>
>>> Please show me what SQL function can handle this!
>>>
>>> SELECT CategoryId,
>>> MAX( CASE seq WHEN 1 THEN ProductName ELSE '' END ) + ' / ' +
>>> MAX( CASE seq WHEN 2 THEN ProductName ELSE '' END ) + ' / ' +
>>> MAX( CASE seq WHEN 3 THEN ProductName ELSE '' END ) + ' / ' +
>>> MAX( CASE seq WHEN 4 THEN ProductName ELSE '' END ) + ' / ' +
>>> MAX( CASE seq WHEN 5 THEN ProductName ELSE '' END ) + ' / ' +
>>> MAX( CASE seq WHEN 6 THEN ProductName ELSE '' END ) + ' / ' +
>>> MAX( CASE seq WHEN 7 THEN ProductName ELSE '' END )
>>> FROM ( SELECT p1.CategoryId, p1.ProductName,
>>> ( SELECT COUNT(*)
>>> FROM Northwind.dbo.Products p2
>>> WHERE p2.CategoryId = p1.CategoryId
>>> AND p2.ProductName <= p1.ProductName )
>>> FROM Northwind.dbo.Products p1
>>> ) D ( CategoryId, ProductName, seq )
>>> GROUP BY CategoryId
>>>
>>> TIA!
>>> Mike D.
>>>
>>>
>>>
>
>