How to combine multi rows to one

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 2005 Forums

Transact-SQL (2005)

How to combine multi rows to one

Author

Topic

fh200300us
Starting Member

28 Posts

Posted - 2008-07-28 : 19:15:09

I have rows like this.
1. Null 1 2
2. 4 Null 2
3. 5 Null 2
4.

Want to get like this
1.4 1 2
2.5 1 2

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-28 : 19:17:20

How do you want to combine it ?

KH
[spoiler]Time is always against us[/spoiler]

fh200300us
Starting Member

28 Posts

Posted - 2008-07-28 : 20:02:08

quote:
Originally posted by khtan

How do you want to combine it ?

KH
[spoiler]Time is always against us[/spoiler]

I want to remove the null and compact the row.

fh200300us
Starting Member

28 Posts

Posted - 2008-07-28 : 20:06:41

quote:
Originally posted by fh200300us

quote:
Originally posted by khtan

How do you want to combine it ?

KH
[spoiler]Time is always against us[/spoiler]

I want to remove the null and compact the row.
or like that
I have rows like this.the third column is same.remove the null filed.I think it should use MAX or SUM. But I forget.
1. Null B A
2. C Null A
3. D Null A

The result is

1.C B A
2.D B A

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-28 : 21:01:42

No easy way of doing it in SQL. Do this in your front end where you are displaying the data

KH
[spoiler]Time is always against us[/spoiler]

fh200300us
Starting Member

28 Posts

Posted - 2008-07-28 : 22:15:48

quote:
Originally posted by khtan

No easy way of doing it in SQL. Do this in your front end where you are displaying the data

KH
[spoiler]Time is always against us[/spoiler]

Using the data to display in reporting service.

fh200300us
Starting Member

28 Posts

Posted - 2008-07-28 : 23:21:20

One solution is like this.
with a as (
select distinct c1,c3 from tb1
where c1 is not null
)
,b as
(
select distinct c2 ,c3 from tb1
where c2 is not null
)
, c as
(
select distinct a.c1, b.c2 ,tb1.c3 from a, b ,tb1
where a.c3 = b.c3 and a.c3 =tb1.c3
group by tb1.c3,a.c1, b.c2
)

select top 2 * from c

C B A
C E A

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-07-29 : 01:32:55

try something like this


DECLARE @T_Table TABLE ( C1 VARCHAR(50), C2 VARCHAR(50), C3 VARCHAR(50))

INSERT INTO @T_Table
SELECT Null, 'B', 'A' UNION ALL
SELECT 'C', Null, 'A' UNION ALL
SELECT 'D', Null, 'A' UNION ALL
SELECT 'E', Null, 'A' UNION ALL
SELECT Null, '1', '2' UNION ALL
SELECT '4',  Null, '2' UNION ALL
SELECT '5',  Null, '2'

SELECT T.C1, T2.C2, T.C3
FROM @T_Table T
LEFT JOIN @T_Table T2 ON T2.C3 = T.C3
		AND T2.C1 IS NULL
WHERE T.C1 IS NOT NULL

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-07-29 : 09:27:20

Beware the following...

DECLARE @T_Table TABLE ( C1 VARCHAR(50), C2 VARCHAR(50), C3 VARCHAR(50))

INSERT INTO @T_Table
SELECT Null, 'B', 'A' UNION ALL
SELECT 'C', Null, 'A' UNION ALL
SELECT 'D', Null, 'A' UNION ALL
SELECT 'E', Null, 'A' UNION ALL
SELECT Null, '1', '2'

SELECT T.C1, T2.C2, T.C3
FROM @T_Table T
LEFT JOIN @T_Table T2 ON T2.C3 = T.C3
		AND T2.C1 IS NULL
WHERE T.C1 IS NOT NULL

/*
C1    C2    C3
----- ----- -----
C     B     A
D     B     A
E     B     A
*/

Note that "Null, '1', '2'" is missed off - if you don't want this, you will need to use a full outer join.

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources