How do I get one row in a result set from 2 rows in parent table without using cursors.

Question

Post reply

How do I get one row in a result set from 2 rows in parent table without using cursors.

ekant_alone

SSCrazy

Points: 2423
More actions
August 11, 2010 at 9:18 pm

#236651

I have a table T with columns A,B,C & D as below:
A, B, C, D
A1,B1,USD,LIBOR
A1,B2,JPY,FIXED
A2,B3,USD,LIBOR
A2,B4,EUR,FIXED
A3,B5,JPY,FIXED
A3,B6,USD,LIBOR
I need to write 2 queries without using cursors, to obtain the result set as below -
Result Set 1:
A1,USD,LIBOR,JPY,FIXED
A2.USD,LIBOR,EUR,FIXED
A3,JPY,FIXED,USD,LIBOR
Result Set 2:
USD,LIBOR,JPY,FIXED
USD,LIBOR,EUR,FIXED
I want to avoid using cursors. Please advise ...

Viewing 11 posts - 1 through 10 (of 10 total)

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

ekant_alone SSCrazy Points: 2423 More actions · Answer 1

I am able to create a temp table which gives me a data set like

A1 USD LIBOR JPY FIXED

A1 JPY FIXED USD LIBOR

A2 USD LIBOR EUR FIXED

A2 EUR FIXED USD LIBOR,

A3 JPY FIXED USD LIBOR

A3 USD LIBOR JPY FIXED

but what next ??

please help.

Toby Harman SSCarpal Tunnel Points: 4168 More actions · Answer 2

What are the rules for this? Is it based on a column in the data being the same?

LutzM SSC Guru Points: 107049 More actions · Answer 3

What is the table structure?

Seems like you're using commas to separate columns in one example and at the same time as result delimiter within one column.

To avoid confusion please provide table def and sample data in a ready use format as described in the first link in my signature. Also please include your expected result based on your sample data.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

ekant_alone SSCrazy Points: 2423 More actions · Answer 4

Excuse me for the confusion

Table is like this:

CREATE TABLE [dbo].[tb1](

[trade] [varchar](10) NULL,

[leg] [varchar](10) NULL, -----say it is my Unique key

[ccy] [varchar](10) NULL,

[idex] [varchar](10) NULL

) ON [PRIMARY]

/* select query */

select top 6 * from dbo.tb1

/*this query gives me the following result*/

trade leg ccy idex

1 a1 b1usdlib

2 a1 b2jpyfix

3 a2 b3jpyfix

4 a2 b4usdlib

5 a3 b5usdlib

6 a3 b6gbbfix

I want a result set like this

trade ccy1 idex1 ccy2 idex2

1 a1 usd lib jpy fix

2 a2 jpy fix usd lib

3 a3 usd lib gbb fix

/* here i want both two currencies and indexes for the tradeid in single row. */

ssiivvaa Grasshopper Points: 13 More actions · Answer 5

Hi,

Can you confirm that you will have only 2 rows of same trade. i.e. is there any possiblity that you will have more than 2 rows for the same trade as mentioned below.

1 a1 b1 usd lib

2 a1 b2 jpy fix

3 a1 b3 abc xyz

4 a2 b4 usd lib

5 a2 b5 abc lib

6 a2 b6 gbb fix

Regards,

Siva

ekant_alone SSCrazy Points: 2423 More actions · Answer 6

No just two records for each trade. however ccy1 and ccy2 , idex1 and idex2 may have same values.

Toby Harman SSCarpal Tunnel Points: 4168 More actions · Answer 7

If there can only be 2 rows, then probably the simplest is to self join table1

So your query ends up something like:

SELECT

t1.leg,

t1.ccy

t1.idex

t2.ccy

t2.idex

FROM tb1 t1

INNER JOIN tb1 t2

ON t1.leg = t2.leg

and t1.trade < t2.trade

You can vary the joins around so that the existance of the second trade is required (INNER JOIN) or not (LEFT OUTER JOIN) etc

NewBeeSQL SSCrazy Points: 2170 More actions · Answer 8

check if this is what yo are looking for result set1

if object_id('tb1') is not null

drop table tb1

CREATE TABLE [dbo].[tb1](

[trade] [varchar](10) NULL,

[leg] [varchar](10) NULL, -----say it is my Unique key

[ccy] [varchar](10) NULL,

[idex] [varchar](10) NULL

) ON [PRIMARY]

insert into tb1

select 'A1','B1','USD','LIBOR'

union all

select 'A1','B2','JPY','FIXED'

union all

select 'A2','B3','USD','LIBOR'

union all

select 'A2','B4','EUR','FIXED'

union all

select 'A3','B5','JPY','FIXED'

union all

select 'A3','B6','USD','LIBOR'

Select id1 = IDENTITY( int,1,1),* into #tempt from [tb1]

;with cte1 as

(

select t1.id1,t1.trade,t1.ccy ccy1,t1.idex idex1,t2.ccy ccy2,t2.idex idex2

from #tempt t1

left join #tempt t2

on (t1.id1 =t2.id1-1)

)

select trade,ccy1,idex1,ccy2,idex2

from cte1 where id1 % 2 = 1

drop table #tempt

requirements are not clear for second result set, if you can eloborate, will try to post tested query for you...

-----------------------------------------------------------------------
For better assistance in answering your questions[/url]
Perforamance Issues[/url]
Cross Tabs and Pivots[/url]
Cross Apply[/url]
The Numbers or Tally Table- Jeff Moden[/url]

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

DROP TABLE #tb1

CREATE TABLE #tb1 (

[trade] [varchar](10) NULL,

[leg] [varchar](10) NULL, -----say it is my Unique key

[ccy] [varchar](10) NULL,

[idex] [varchar](10) NULL

)

INSERT INTO #tb1 (trade, leg, ccy, idex)

SELECT 'a1', 'b1', 'usd', 'lib' UNION ALL

SELECT 'a1', 'b2', 'jpy', 'fix' UNION ALL

SELECT 'a2', 'b3', 'jpy', 'fix' UNION ALL

SELECT 'a2', 'b4', 'usd', 'lib' UNION ALL

SELECT 'a3', 'b5', 'usd', 'lib' UNION ALL

SELECT 'a3', 'b6', 'gbb', 'fix'

;WITH OrderedSet AS (

SELECT TradeID = ROW_NUMBER() OVER(PARTITION BY trade ORDER BY trade, leg),

trade, leg, ccy, idex

FROM #tb1

)

SELECT o1.trade, o1.ccy, o1.idex, o2.ccy, o2.idex

FROM OrderedSet o1

INNER JOIN OrderedSet o2 ON o2.trade = o1.trade AND o2.TradeID = 2

WHERE o1.TradeID = 1

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ekant_alone SSCrazy Points: 2423 More actions · Answer 10

@new Bee and @ sqlCrazy.

You Guyz Rock. It worked. CTE was it....why it didn't strike in my mind? Stupid me. 🙂

Thanks a ton.

Have a great weekend.