Combine Rows to one - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Combine Rows to one

Author

Topic

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2008-12-12 : 04:36:24

Hi,

Any suggestions to how i can combine rows to one where the ID is identical.
Data:
ID Description Date Task Task2
5157 WW Combo Tara Swanson 11 Dec 08 Garron N
5157 WW Combo Tara Swanson 11 Dec 08 N R&D

I want the result to be:
5157 WW Combo Tara Swanson 11 Dec 08 Garron R&D

Any ideas

soorajtnpki
Posting Yak Master

231 Posts

Posted - 2008-12-12 : 04:58:04

hi

In that data 'N' denotes what ?

and also if data is like below, what will be ur output

ID Description Date Task Task2
5157 WW Combo Tara Swanson 11 Dec 08 Garron N
5157 WW Combo Tara Swanson 11 Dec 12 TS R&D

ok tanx

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2008-12-12 : 04:58:09

select t.ID,t.Description,t.Date,t.Task,coalesce(t.Task2) + ' ' + coalesce(t1.Task2) from urtable t join urtable t1 on t1.id = t.id

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2008-12-12 : 05:01:51

I think N denotes NULL

Jai Krishna

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2008-12-12 : 05:12:35

Hi, It's a bit bigger than just combining as was I initially thought.

I see there is more columns, as well the rows have different grpID's makinging them to be seprate rows
grpID 1 2 3 4
5157 WW 1881 N Garron N N
5157 WW 1882 N N R&D N
5157 WW 1883 Mkt N N N
5157 WW 1884 N N N Cst

Theay a split by different ID, what I'm surpose to do is like an overview of one row per ProcessID which is eg.5157

Result being: 5157 WW 1881 Mkt Garron R&D Cst

Please Assist!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-12-12 : 07:49:28

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-12 : 09:25:50

[code]SELECT grpID,MIN(firstfield),MAX(1),MAX(2),MAX(3),MAX(4)
FROM Table
GROUP BY grpID[/code]

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-12-12 : 10:08:57

quote:
Originally posted by visakh16

SELECT grpID,MIN(firstfield),MAX(1),MAX(2),MAX(3),MAX(4)
FROM Table
GROUP BY grpID

MAX(1),MAX(2),MAX(3),MAX(4)

will always give results:- 1,2,3,4

You meant MAX([1]),MAX([2]),MAX([3]),MAX([4])

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-12 : 10:25:16

quote:
Originally posted by sakets_2000

quote:
Originally posted by visakh16

SELECT grpID,MIN(firstfield),MAX(1),MAX(2),MAX(3),MAX(4)
FROM Table
GROUP BY grpID

MAX(1),MAX(2),MAX(3),MAX(4)
will always give results:- 1,2,3,4

You meant MAX([1]),MAX([2]),MAX([3]),MAX([4])

yup...1,2,3,4 columns
i dont think that's the actual column name. OP gave it for illustration i guess.

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2008-12-19 : 06:26:48

Thank You All
Apologies for the late reply

Unfortunately not winning.

The rows have unique ID's being grpID:

Proc grpID 1 2 3 4
5157 1881 N Garron N N
5157 1882 N N R&D N
5157 1883 Mkt N N N
5157 1884 N N N Cst

Result being: 5157 Mkt Garron R&D Cst

Regards

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2008-12-19 : 07:20:00

Hi. Follow the first link in my signature and you are sure to get an answer.

[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-12-19 : 08:11:19

[code]DECLARE @Sample TABLE
(
procID INT,
grpID INT,
col1 VARCHAR(20),
col2 VARCHAR(20),
col3 VARCHAR(20),
col4 VARCHAR(20)
)

INSERT @Sample
SELECT 5157, 1881, 'N', 'Garron', 'N', 'N' UNION ALL
SELECT 5157, 1882, 'N', 'N', 'R&D', 'N' UNION ALL
SELECT 5157, 1883, 'Mkt', 'N', 'N', 'N' UNION ALL
SELECT 5157, 1884, 'N', 'N', 'N', 'Cst'

SELECT procID,
COALESCE(MAX(NULLIF(col1, 'N')) + ' ', '')
+ COALESCE(MAX(NULLIF(col2, 'N')) + ' ', '')
+ COALESCE(MAX(NULLIF(col3, 'N')) + ' ', '')
+ COALESCE(MAX(NULLIF(col4, 'N')) + ' ', '') AS miscellaneous
FROM @Sample
GROUP BY procID
ORDER BY procID[/code]

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

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2008-12-22 : 04:57:19

Thank You very much, This is really on another level for me.

Declaring a var & using the coalesce worked great.

Finally a break through and some rest.

Thank You All

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-22 : 05:53:41

No, you DO NOT need to declare a variable.
The first part is only to mimic your environment, of which I have no access.

This is the part you should be concentrating on

SELECT		procID,
		RTRIM(COALESCE(MAX(NULLIF(col1, 'N')) + ' ', '')
		+ COALESCE(MAX(NULLIF(col2, 'N')) + ' ', '')
		+ COALESCE(MAX(NULLIF(col3, 'N')) + ' ', '')
		+ COALESCE(MAX(NULLIF(col4, 'N')) + ' ', '')) AS miscellaneous
FROM		{Your table name here}
GROUP BY	procID
ORDER BY	procID

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

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2008-12-22 : 06:08:05

Thanks - this is exactly what i did as I started with the declaring the var & later saw it was not needed & used the from table part - to another select returning the rows.

I filtered out the declaring the var as it is a learning curve and would be used for future use.

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources