Select Multiple Rows as a Single TUPLE? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Select Multiple Rows as a Single TUPLE?

Author

Topic

bluemetal
Starting Member

26 Posts

Posted - 2005-04-27 : 19:15:55

Hi, this is a SQL Server Data related question. I get data imports which end up in the table in the following manner:

ID POLICY DESC VAL
1 HM03 GST 4.0
2 HM03 FSL 5.3
3 HM03 SDT 2.5

4 HM04 GST 8.0
5 HM04 FSL 3.1
6 HM04 SDT 4.2

If you notice, the records 1,2,3 are just ONE TUPLE .. meaning the Query i need to write should end up showing this recordset like this

POLICY GST FSL SDT
HM03 4.0 5.3 2.5

We can ignore the ID. Can anyone help me with this Query? Its urgent .. Below is what i'm trying to do, Thank you.

select a.Policy_number,
a.feesval 'FSL', b.feesval 'GST', c.feesval 'SDT',
a.Commision, a.Agent_Fee, a.Agent_Fee_GST
From
tbl_XMLPayments a, tbl_XMLPayments b, tbl_XMLPayments c
where b.policy_number = a.policy_number
and c.policy_number = a.policy_number
and a.feesdesc = 'FSL'
and b.feesdesc = 'GST'
and c.feesdesc = 'SDT'

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-04-27 : 19:18:25

You want a cross-tab, this should do the trick:

SELECT Policy,
Sum(CASE WHEN [DESC]='GST' THEN Val END) AS GST,
Sum(CASE WHEN [DESC]='FSL' THEN Val END) AS FSL,
Sum(CASE WHEN [DESC]='SDT' THEN Val END) AS SDT
FROM tbl_XMLPayments
GROUP BY Policy

bluemetal
Starting Member

26 Posts

Posted - 2005-04-27 : 19:46:43

Hi Rob,
Your QUERY nearly did the job, although what it does is that it combines (sums) up the two tuples, i wanted the tuples to remain seperate .. i mean one set of FSL, GST, SD to remain seperate from the other .. not get summed. When i run your query, it adds up Both GST's and shows 12 (8.0 + 4.0).

Any more ideas PLEASE? Thank you.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-04-27 : 19:50:08

It can't, it's grouping by Policy, each distinct Policy value would form its own group.

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-04-27 : 20:34:20

bluemetal, are you sure?



DECLARE @deez TABLE (policy_id int, policy varchar(4), policy_des varchar(3), policy_val dec(2,1))
INSERT INTO @deez 
	SELECT 1,'HM03','GST',4.0 UNION SELECT 2,'HM03','FSL',5.3 UNION SELECT 3,'HM03','SDT',2.5 UNION
	SELECT 4,'HM04','GST',8.0 UNION SELECT 5,'HM04','FSL',3.1 UNION SELECT 6,'HM04','SDT',4.2 

-- select * from @deez

SELECT 	policy, 
	Sum(CASE WHEN policy_des ='GST' THEN policy_val ELSE 0 END) AS GST,
	Sum(CASE WHEN policy_des ='FSL' THEN policy_val ELSE 0 END) AS FSL,
	Sum(CASE WHEN policy_des ='SDT' THEN policy_val ELSE 0 END) AS SDT
FROM @deez 
GROUP BY policy

bluemetal
Starting Member

26 Posts

Posted - 2005-04-27 : 23:39:29

Hey NathanS:

Actually what happens is that i get another set of rows for a different date but for the SAME Policy Number .. Now that will surely add up the FSL, GST, SDT and destroy the individual existence of the tuple.

That is what i was trying to refer to, i guess i couldn't explain. I'm still fishing for solutions.

bluemetal
Starting Member

26 Posts

Posted - 2005-04-28 : 01:49:02

Thanks you guys! I finally figured a workaround, added an identity column that keeps the tuples individually identified. So far so good, since this was a once off task, i'mnot worrying about it much, but i'm sure there are better ways to do it. :)

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-04-28 : 14:43:08

yea, different dates would be summed together. You can either add the date qualification to the CASE, or like you said, add an identity field and use that within the CASE.

- Nathan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources