Omstrukturering i Output fra SQL

Det kan sagtens lade sig gøre. I din løkke, som henter fra databasen, skal du bare gemme oplysningerne i 2 strings. F.eks.:

Do While ...

  strDescription = rs("description")

  strHead = strHead & "Last Month_"&strDescription & "Last Year_"&strDescription & "Percent_"&strDescription

  strOutput = strOutput & rs("LastMonth")&"_"&strDescription & rs("LastYear")&"_"&strDescription & rs("Percent")&"_"&strDescription

Loop

Response.Write strHead
Response.Write strOutput

Hvis du ønsker en løsning i ren SQL, var en mulighed:

SELECT SUM(CASE WHEN Description = 'Test' THEN AccLastMonthThisYear ELSE 0 END) AS LastMonth_Test, SUM(CASE WHEN Description = 'Test' THEN AccLastMonthLastYear ELSE 0 END) AS LastYear_Test, SUM(CASE WHEN Description = 'Test' THEN AccChangeLastMonth ELSE 0 END) AS Percent_Test, SUM(CASE WHEN Description = 'Test2' THEN AccLastMonthThisYear ELSE 0 END) AS LastMonth_Test2, SUM(CASE WHEN Description = 'Test2' THEN AccLastMonthLastYear ELSE 0 END) AS LastYear_Test2, SUM(CASE WHEN Description = 'Test2' THEN AccChangeLastMonth ELSE 0 END) AS Percent_Test2, SUM(CASE WHEN Description = 'Test3' THEN AccLastMonthThisYear ELSE 0 END) AS LastMonth_Test3, SUM(CASE WHEN Description = 'Test3' THEN AccLastMonthLastYear ELSE 0 END) AS LastYear_Test3, SUM(CASE WHEN Description = 'Test3' THEN AccChangeLastMonth ELSE 0 END) AS Percent_Test3
FROM SalesStatistics
WHERE CustomerID= 182722

For at få alle rækker i tabellen samlet til én række, er man nødt til at bruge en aggregat funktion. Her er brugt SUM.

Send et svar kjulius!

Okay, det være hermed gjort...