rows to column - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

rows to column

Author

Topic

imfairozkhan
Starting Member

11 Posts

Posted - 2008-11-19 : 05:36:45

hi guys
i have requirement in below given form

exiting table
field1 field2 field3
a1 b1 c1
a2 b2 c2
a3 b3 c3
a4 b4 c4

output required
field1 field2 field3 field4 field5 field6
a1 b1 c1 a2 b2 c2
a3 b3 c3 a4 b4 c4

any help is highly appreciated
thakns
fairozkhan

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-19 : 05:39:05

Use the PIVOT operator in SQL Server 2005.
If you are using an earlier version, see CROSSTAB or PIVOT queries here at SQLTeam.

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-19 : 06:02:45

just wants to merge consecutive two records to one?

imfairozkhan
Starting Member

11 Posts

Posted - 2008-11-19 : 06:11:15

quote:
Originally posted by Peso

Use the PIVOT operator in SQL Server 2005.
If you are using an earlier version, see CROSSTAB or PIVOT queries here at SQLTeam.

E 12°55'05.63"
N 56°04'39.26"

hi
can u please write some sample of pivot related to my requirement. i dont need any calculation to be done, the only thing what i need to to place two records in one record position.

thanks

imfairozkhan
Starting Member

11 Posts

Posted - 2008-11-19 : 06:16:36

quote:
Originally posted by visakh16

just wants to merge consecutive two records to one?

s may be something like that

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-19 : 06:44:28

quote:
Originally posted by imfairozkhan

quote:
Originally posted by visakh16

just wants to merge consecutive two records to one?

s may be something like that

may be? so you're not sure?

imfairozkhan
Starting Member

11 Posts

Posted - 2008-11-19 : 07:21:03

quote:
Originally posted by visakh16

quote:
Originally posted by imfairozkhan

quote:
Originally posted by visakh16

just wants to merge consecutive two records to one?

s may be something like that

may be? so you're not sure?

s im sure, but i need only two records at a time as one record

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2008-11-19 : 07:25:17

What they are trying to do, is solicit information from you. What YOU are trying to do (it seems), is have them do your homework for you. Show us what code you have so far.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-19 : 07:27:27

[code]DECLARE @Sample TABLE
(
field1 char(2),
field2 char(2),
field3 char(2)
)

INSERT @Sample
SELECT 'a1', 'b1', 'c1' UNION ALL
SELECT 'a2', 'b2', 'c2' UNION ALL
SELECT 'a3', 'b3', 'c3' UNION ALL
SELECT 'a4', 'b4', 'c4'

;WITH Yak (col1, col2, col3, row, grp)
AS (
SELECT field1, field2, field3, (row_number() Over (order by field1)-1)/2,
(row_number() Over (order by field1)-1) % 2
FROM @sample
)

SELECT max(case when grp = 0 then col1 else null end) AS field1,
max(case when grp = 0 then col2 else null end) AS field2,
max(case when grp = 0 then col3 else null end) AS field3,
max(case when grp = 1 then col1 else null end) AS field4,
max(case when grp = 1 then col2 else null end) AS field5,
max(case when grp = 1 then col3 else null end) AS field6
FROM Yak
group by row
order by row[/code]

E 12°55'05.63"
N 56°04'39.26"

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2008-11-19 : 10:04:48

create table #field (field1 varchar(32),field2 varchar(32),field3 varchar(32),field4 varchar(32))

insert into #field select 'a1','b1','c1' union all
select 'a2','b2','c2' union all
select 'a3','b3','c3'

select row_number() over(order by field1) as rowid ,* into #fields from #field

select f2.field1,f2.field2,f2.field3,f1.field1 as field4,f1.field2 AS field5 ,f1.field3 as field6
from (select row_number() over(order by field1) as rwid,* from #fields where rowid % 2 = 0) as f1
inner join
(select row_number() over(order by field1) as rid,* from #fields where rowid % 2 <> 0) as f2 on f1.rwid = f2.rid

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources