Summary with string - 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 2000 Forums

Transact-SQL (2000)

Summary with string

Author

Topic

mrday
Starting Member

2 Posts

Posted - 2004-08-19 : 20:49:50

Hi there...

I have data like this

Code Field1 Field2
-------------------------------------
00001 K-198
00001 K-2000

How to create result like this with single query not store procedure

Code Field1 Field2
-----------------------------------------
00001 K-198 K-2000

Ok Thank's

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-08-19 : 22:13:56

See this article http://www.sqlteam.com/item.asp?ItemID=11021 and it's comments http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=19647

--Ken
"Knowledge is a process of piling up facts; wisdom lies in their simplification."

mrday
Starting Member

2 Posts

Posted - 2004-08-20 : 00:16:25

Sorry, my data should like this

Code Field1 Field2
------------------
0001 K-198 (NULL)
0001 (NULL) K-2000

and the result is

Code Field1 Field2
------------------
0001 K-198 K-2000

I mean result is not single field but three field

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-08-20 : 00:30:46

Select Code, Max(Field1), Max(Field2)
From table
Group by Code

Kristen
Test

22859 Posts

Posted - 2004-08-20 : 03:01:49

That's gonna give ANSI Warnings about the NULLs, which may be a PITA if processed through ADO ...

Might need to do some ghastly hack like


Select Code, 
      Max(COALESCE(Field1, '')),
      Max(COALESCE(Field2, ''))
From table
Group by Code

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources