[MS SQL] Group by perikelen

[PdeBie]

Hoi allen,

Ik heb een aantal rijen, die ik wil samenvoegen in een nieuwe tabel tot 1 rij.

SQL:

SELECT 
    tap.Code, 
    tap.Name, 
    tap.[Description],
    (CASE tap.Attribute1 WHEN 5 THEN tap.Price END) AS MinValue,
    (CASE tap.Attribute1 WHEN 6 THEN tap.Price END) AS MiddleValue,
    (CASE tap.Attribute1 WHEN 7 THEN tap.Price END) AS HighValue,
    tap.[Type], 
    c.ID AS [CountryID]
FROM 
        #TempAvailableProducts tap JOIN Country c ON tap.Country = c.CountryCode COLLATE SQL_Latin1_General_CP1_CI_AS

Geeft mij de volgende tabel

Code	Name	Description	MinValue	MiddleValue	HighValue	Type	CountryID
12RM	Dozen Roses	NULL	50,00	NULL	NULL	8	36
12RM	Dozen Roses	NULL	NULL	75,00	NULL	8	36
12RM	Dozen Roses	NULL	NULL	NULL	100,00	8	36
12RM	Dozen Roses	NULL	50,00	NULL	NULL	9	36
12RM	Dozen Roses	NULL	NULL	75,00	NULL	9	36
12RM	Dozen Roses	NULL	NULL	NULL	100,00	9	36

Dit wil ik groeperen tot:

Code	Name	Description	MinValue	MiddleValue	HighValue	Type	CountryID
12RM	Dozen Roses	NULL	50,00	75,00	100,00	8	36
12RM	Dozen Roses	NULL	50,00	75,00	100,00	9	36

Dus ik dacht:

SQL:

SELECT 
    tap.Code, 
    tap.Name, 
    tap.[Description],
    (CASE tap.Attribute1 WHEN 5 THEN tap.Price END) AS MinValue,
    (CASE tap.Attribute1 WHEN 6 THEN tap.Price END) AS MiddleValue,
    (CASE tap.Attribute1 WHEN 7 THEN tap.Price END) AS HighValue,
    tap.[Type], 
    c.ID AS [CountryID]
FROM 
        #TempAvailableProducts tap JOIN Country c ON tap.Country = c.CountryCode COLLATE SQL_Latin1_General_CP1_CI_AS
GROUP BY 
    tap.IntercatCode, 
    tap.Name, 
    tap.[Description],
    tap.Attribute1,
    tap.Price,
    tap.[Type], 
    c.ID

Maar dan krijg ik de error The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.. Dit treedt op vanwege de kolom Description.
Als ik die uit de select en de group by haal, krijg ik het volgende resultaat.

Code	Name	MinValue	MiddleValue	HighValue	Type	CountryID
12RM	Dozen Roses	50,00	NULL	NULL	8	36
12RM	Dozen Roses	50,00	NULL	NULL	9	36
12RM	Dozen Roses	NULL	75,00	NULL	8	36
12RM	Dozen Roses	NULL	75,00	NULL	9	36
12RM	Dozen Roses	NULL	NULL	100,00	8	36
12RM	Dozen Roses	NULL	NULL	100,00	9	36

Hoe krijg ik het nou als gewenst? En dan het liefst incl. de description kolom, omdat die bij sommige records wel gevuld is.

[P_de_B]

Waarom is Description een TEXT veld en niet een (N)(VAR)CHAR ? TEXT kan gebruikt worden als je heel veel data kwijt moet. Ik denk dat je het beste het datatype kunt aanpassen.

[PdeBie]

geen idee. Is niet mijn database. Ik moet data uit deze database importeren. Kan dus ook niet zomaar het type wijzigen helaas. Zou een Convert in de select kunnen helpen?

[ Voor 14% gewijzigd door PdeBie op 14-03-2013 15:44 ]

[Dido]

Los daarvan, die drie kolommen die je middels die case selecteert wil je niet op groeperen in deze vorm aangezien je aparte records krijgt voor de null-values.
Iets als

code:

SUM((CASE tap.Attribute1 WHEN 7 THEN tap.Price ELSE 0 END) AS HighValue, 

...
group by Highvalue

?

[DamadmOO]

Je kan niet groeperen op een kolom alias (en al helemaal niet als die het resultaat is van een aggregatie).

Maar de volgende code zou moeten doen wat je wilt:

SQL:

SELECT  
    tap.Code,  
    tap.Name,  
    CAST(tap.[Description] AS NVARCHAR(MAX)) AS Description, 
    MIN(CASE tap.Attribute1 WHEN 5 THEN tap.Price END) AS MinValue, 
    MIN(CASE tap.Attribute1 WHEN 6 THEN tap.Price END) AS MiddleValue, 
    MIN(CASE tap.Attribute1 WHEN 7 THEN tap.Price END) AS HighValue, 
    tap.[Type],  
    c.ID AS [CountryID] 
FROM  
        #TempAvailableProducts tap JOIN Country c ON tap.Country = c.CountryCode COLLATE SQL_Latin1_General_CP1_CI_AS 
GROUP BY  
    tap.IntercatCode,  
    tap.Name,  
    CAST(tap.[Description] AS NVARCHAR(MAX)), 
    tap.[Type],  
    c.ID

[ Voor 4% gewijzigd door DamadmOO op 14-03-2013 15:47 ]

[Dido]

DamadmOO schreef op donderdag 14 maart 2013 @ 15:46:
Je kan niet groeperen op een kolom alias (en al helemaal niet als die het resultaat is van een aggregatie).

My bad (die alias is het probleem natuurlijk niet, maar die sum is al genoeg, dan hoef je uiteraard niet meer te groeperen.

(of je sum, min of max gebruikt is natuurlijk arbitrair als er maar 1 waarde voorkomt, maar ik ken de database natuurlijk niet )

Sowieso moeten inderdaad de price en attribute1 uit de group by natuurlijk

[PdeBie]

@DamadmOO: Die lijkt te werken inderdaad. Top!

Ik riep een Convert in mijn vorige post, maar een Cast werkt natuurlijk net zo goed (of misschien zelfs sneller).

[ Voor 55% gewijzigd door PdeBie op 14-03-2013 15:51 ]

[DamadmOO]

Convert en Cast doen onder water precies hetzelfde, met als verschil dat je in een convert wat formaterings mogelijkheden hebt (voornamelijk als je vanaf een datetime naar een string gaat).