SQL syntax GROUP BY or something like that

Question

SQL syntax GROUP BY or something like that

Jacques Lachance

SSC Rookie

Points: 47
More actions
November 24, 2004 at 1:47 pm

#163287

Hi,
I have to table like:
Table Customers
Fields CustomerID,Name
Example
1, John
2, Mary
3, Bill

Table Sales
Fields CustomerID,Year,Total
Eaxmple:
1,2001,10
1,2002,15
1,2003,20
2,2001,35
2,2002,35
2,2003,50
3,2002,32
3,2003,40

I'm trying to build a SQL SELECT statment to obtain something like
Client,year1,total1,year2,total2,year3,total3,...
Example
1,2001,10,2002,15,2003,20
2,2001,35,2002,35,2003,50
3,2002,32,2003,40
Is it possible?
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

While the "best" answer is to do this at your presentation layer, you might want to search the script section or the site in general for crosstab report (or pivot for that matter).

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Yuvraj Powar SSC Veteran Points: 272 More actions · Answer 2

Hi,

with help of single select statement I don't think so it is possible . but u do this by using some programing logic like this

--CREATE TABLE FOR JUST STORING THE DISTINCT Client, WHICH WE'LL USE FOR MAKING

--THE COMMA SEPARATED CONCATENATED STRING OF year and total

CREATE TABLE #TEMP_ISSUER

(

SEQ_NO INT IDENTITY,

CLIENT INT

)

INSERT INTO #TEMP_ISSUER

SELECT DISTINCT CLIENT

FROM SALES

DECLARE @MAX_CTR INT

DECLARE @I INT

DECLARE @S_OPSTRING(4000)

SELECT @S_OPSTRING= ''

SELECT @MAX_CTR = COUNT(*) FROM #TEMP_INV

SELECT @I = 1

WHILE @I <= @MAX_CTR

BEGIN

/*HERE ACCESS EACH ROW AND TAKE A CONCATNET THE REQ, COLUMNS NAD UPDATE THEM INTO ANOTHER TEMP TABLE AND FINAL SELECT WILL BE SELECT OF TEMP TABLE. */

SELECT @I = @I +1

SELECT @S_INVGRP = ''

END

REGARDS

YUVRAJ

PBirch SSCrazy Eights Points: 8493 More actions · Answer 3

We have a "dumb" report writer system. It requires inner joins for everything. For a group of users I had to create crosstab tables. Do a search on pivot tables and crosstabs. Mine is something like:

SELECT

Blah

, MIN(CASE tBlah.ln when 1 THEN tBlah.emc_id else NULL END) as tBlah_ID1

, MIN(CASE tBlah.ln when 2 THEN tBlah.emc_id else NULL END) as tBlah_ID2

, MIN(CASE tBlah.ln when 3 THEN tBlah.emc_id else NULL END) as tBlah_ID3

, MIN(CASE tBlah.ln when 4 THEN tBlah.emc_id else NULL END) as tBlah_ID4

@tempBlah tBlah

GROUP BY Blah

The trick is to add a sequence number. I also used table variables rather than temp tables.

Quand on parle du loup, on en voit la queue

Yoda-193403 SSC Eights! Points: 988 More actions · Answer 4

dumb report writer, hm? Normally presentation layer, you should do this. Perhaps in this case not possible.

Learn the power of the pivot query, you will! But this query, not so simple. A ranking, you must create:

select distinct customerID, year, 
  (select count(distinct year) 
   from YourData B
   on B.CustomerID = A.CustomerID AND
      B.Year <= A.Year) as Rank
from YourData A

Review the above, you will. learn it. know it. it is a powerful tool of a true jedi! Temp tables, table variables, a jedi uses not these items carelessly. Only in dire need. Required now, they are not!

Armed with the above, ready now you are to continue!

select customerID, 
  Max(case when Rank=1 then then Year else Null END) as Year1,
  SUM(case when Rank=1 then then Value else 0 END) as Year1Total,
  Max(case when Rank=2 then then Year else Null END) as Year2,
  SUM(case when Rank=2 then then Value else 0 END) as Year2Total,
  Max(case when Rank=3 then then Year else Null END) as Year3,
  SUM(case when Rank=3 then then Value else 0 END) as Year3Total,
  Max(case when Rank=4 then then Year else Null END) as Year4
  SUM(case when Rank=4 then then Value else 0 END) as Year4Total
  ...
from
  (above SQL that does the ranking) a
group by 
  CustomerID

The power of the T-SQL is strong! Use it wisely.