Problem regarding compact a table

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

Problem regarding compact a table

Author

Topic

legendgod
Starting Member

6 Posts

Posted - 2011-02-22 : 22:58:04

Dear all,

I am first time to SQL team forum. Nice to meet you all.

I have a table like follow:
[id] [code]
13 ADM
226 ADM
59 ADM
259 ADM
151 ADM
192 ADM
193 COA
152 COA
260 COA
60 COA
227 COA

How can I return a table like follow?
ADM 13,226,59,259,151,192
COA 193,152,260,60,227

Thanks for your input.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2011-02-22 : 23:11:38

If you are using SQL Server 2005 or higher, then you can use Pivot operator.
Have a look of pivot operator in books online.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2011-02-22 : 23:13:06

If [code] have many values and you want to extract all dynamically then have a look at:

http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

legendgod
Starting Member

6 Posts

Posted - 2011-02-22 : 23:35:42

Thanks for replies!

I am reading on PIVOT (but it is quite difficult to understand for my level...)

Beside, is there any SQL function to merge several string (e.g. 13 226 59) into string like "13,226,59" ? Do I need to write a function myself? Thanks.

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-23 : 00:00:31

quote:
Originally posted by legendgod

Column1|Column2
ADM|13,226,59,259,151,192
COA|193,152,260,60,227

if the required result is two column data, then perhaps you are looking for a function which transforms "Tabular values to CSV(comma separated values)". Which should then be used in a While loop for every distinct Code

Cheers
MIK

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-02-23 : 01:52:33

perhaps this is what you need. http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

something like this


create table #temp
(
	[id] 	varchar(10),
	code	varchar(10)
)

insert into #temp
select	 13 ,'ADM' union all
select	226 ,'ADM' union all
select	 59 ,'ADM' union all
select	259 ,'ADM' union all
select	151 ,'ADM' union all
select	192 ,'ADM' union all
select	193 ,'COA' union all
select	152 ,'COA' union all
select	260 ,'COA' union all
select	 60 ,'COA' union all
select	227 ,'COA'

select	code] stuff((select ',' + [id] from #temp x where x.code = t.code order by [id] for xml path('')), 1, 1, '')
from	#temp t
group by code

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

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-02-23 : 07:40:48

quote:

am reading on PIVOT (but it is quite difficult to understand for my level...)

Beside, is there any SQL function to merge several string (e.g. 13 226 59) into string like "13,226,59" ? Do I need to write a function myself? Thanks.

It's hard and unnatural because.... it's unnatural to do this in the database.

Sure, there are many ways to do it -- they are all *wrong*. (not that they don't *work* but that this is a task for whatever application or front end is consuming the results).

If you think about it it would be really, really easy to write a simple wrapper round the results in c# or whatever to format them the way you want to.

Regards,
Charlie.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

legendgod
Starting Member

6 Posts

Posted - 2011-02-25 : 00:04:34

Thanks for all your reply. I have created a temp table with desired format.

Now I have problem to combine multiple SQL statement into single SP. My SP code as follow:


SET @sql_string = ' prepare #temp '
SET @sql_string2 = ' rework #temp and insert into #temp2 using STUFF'
SET @sql_string3 = 'select * from #temp2'
SET @sql_droptable = 'drop table #temp2;drop table #temp'
	exec(@sql_string)
	exec(@sql_string2)
	exec(@sql_string3)
	exec(@sql_droptable)

I tried run the statement one by one and get the result I need. However when combine into above SP, the return result is a blank table (#temp2). What did I missed? Thanks.

legendgod
Starting Member

6 Posts

Posted - 2011-02-25 : 02:11:35

Thanks I solve it myself by:

set @sql_stringcombo = @sql_string + @sql_string2 + 'select * from #temp2
exec(@sql_stringcombo)
exec(@sql_droptable)

Thank you so much for everyone

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-02-25 : 02:38:54

what does prepare ? rework ? are you using SQL Server ?

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

legendgod
Starting Member

6 Posts

Posted - 2011-02-25 : 03:18:46

Yes I am using SQL 2005

'Prepare' and 'rework' are not SQL statement. I just try to hide the actual code to make my post clearer.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources