Query

Question

Post reply

Query

RaviShankar1234

SSCommitted

Points: 1647
More actions
December 29, 2010 at 3:47 pm

#244818

This is the Table structure I have I want to write a query to the get the
col 1 Col2
1A
2B
3C
4C
5D
6E
O/p should be like this
col 1 Col2 Col3 Col4
1 A 2 B
3 C 4 D
5 E 6 F
Can any one help me with this query.
Thanks,
Ravi

Viewing 9 posts - 1 through 8 (of 8 total)

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

Please provide the business rule for your conversion.

How does "F" show up all of a sudden?

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]

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 2

LutzM (12/29/2010)
Please provide the business rule for your conversion.
How does "F" show up all of a sudden?

To add to this, why did C 3/4 only apply to 3 and D/E subtract 1 from their numeric?

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 3

RaviShankar1234 (12/29/2010)
This is the Table structure I have I want to write a query to the get the
col 1 Col2
1A
2B
3C
4C
5D
6E
O/p should be like this
col 1 Col2 Col3 Col4
1 A 2 B
3 C 4 D
5 E 6 F
Can any one help me with this query.
Thanks,
Ravi

Hi

I think this is what you are looking for

declare @Temp table (Rid int identity,col1 int ,Col2 varchar(2))

declare @Temp1 table (Rid int ,col1 int ,Col2 varchar(2),col3 int ,Col4 varchar(2))

/*Data insert with identity to @Temp */

insert into @Temp

Select 1,'A'

Union Select 2,'B'

Union Select 3,'C'

Union Select 4,'D'

Union Select 5,'E'

Union Select 6,'F'

/*Insert your @Temp1 value two columns then need to update remaning two columns*/

insert into @Temp1

Select rid,col1,Col2,null,null from @Temp where rid%2=1

Update a set a.col3=b.col1,a.Col4=b.Col2 from @Temp1 as a join @Temp as b on a.Rid=b.Rid-1

Select col1,col2,col3,col4 from @Temp1

Thanks

Parthi

Thanks
Parthi

RaviShankar1234 SSCommitted Points: 1647 More actions · Answer 4

--------------------------------------------------------------------------------

This is the Table structure I have I want to write a query to the get the

col 1 Col2

1 A

2 B

3 C

4 D

5 E

6 F

I have a table with 2 columns and I need to write a query such away that I need to get the output as below with 4 columns

col 1 Col2 Col3 Col4

1 A 2 B

3 C 4 D

5 E 6 F

Can you please help me.

Thanks,

Ravi

LutzM SSC Guru Points: 107049 More actions · Answer 5

something like this?

DECLARE @tbl TABLE

(

col1 INT, Col2 CHAR(1)

)

INSERT INTO @tbl

SELECT 1 ,'A' UNION ALL

SELECT 2 ,'B' UNION ALL

SELECT 3 ,'C' UNION ALL

SELECT 4 ,'D' UNION ALL

SELECT 5 ,'E' UNION ALL

SELECT 6 ,'F'

SELECT *

FROM @tbl t1

INNER JOIN @tbl t2 ON t1.col1=t2.col1-1 AND t1.col1%2=1

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]

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 6

RaviShankar1234 (12/30/2010)
--------------------------------------------------------------------------------
This is the Table structure I have I want to write a query to the get the
col 1 Col2
1 A
2 B
3 C
4 D
5 E
6 F
I have a table with 2 columns and I need to write a query such away that I need to get the output as below with 4 columns
col 1 Col2 Col3 Col4
1 A 2 B
3 C 4 D
5 E 6 F
Can you please help me.
Thanks,
Ravi

Hi

I think i have given solution for you in above post it is similar u need to adopt your logic thats it.

declare @Temp table (Rid int identity,col1 int ,Col2 varchar(2))

declare @Temp1 table (Rid int ,col1 int ,Col2 varchar(2),col3 int ,Col4 varchar(2))

/*Data insert with identity to @Temp */

insert into @Temp

Select 1,'A' /*Keep it as your data */

Union Select 2,'B'

Union Select 3,'C'

Union Select 4,'D'

Union Select 5,'E'

Union Select 6,'F'

/*Insert your @Temp value with Rid in odds */

insert into @Temp1

Select rid,col1,Col2,null,null from @Temp where rid%2=1

/*Updating your @Temp value with Rid in Even */

Update a set a.col3=b.col1,a.Col4=b.Col2 from @Temp1 as a join @Temp as b on a.Rid=b.Rid-1

/*final Output*/

Select col1,col2,col3,col4 from @Temp1

Thanks

Parthi

Thanks
Parthi

LutzM SSC Guru Points: 107049 More actions · Answer 7

@parthi:

Your solution is based on copying the data to a staging table followed by an update, whereas a rather simple self-join will do the trick...

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]

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 8

LutzM (12/30/2010)
@Parthi:
Your solution is based on copying the data to a staging table followed by an update, whereas a rather simple self-join will do the trick...

@LutzM

Suddenly join did not strick for me.I agree that self join is enough for the above query.My aim was just to derive the o/p based on some condition.Anyway thanks for correcting me.

Parthi

Thanks
Parthi