October 30, 2008 at 8:08 pm
Hi,
I am facing a problem to extract data from a table, the table contains over 41k records, and some of them(>5k) share same column value, now what i am requested is to merge records share same column value.
Take an example as following
Name Value Status
ABC ANYTHING RED
DEF SOMETHING GREEN
ABC ANYTHING GREEN
XYZ NOTHING YELLOW
I need to run a query that give me output like this, combine 2 records into 1, is this can be done?
Name Value Status
ABC ANYTHING RED, GREEN
DEF ANYTHING GREEN
XYZ NOTHING YELLOW
appreicate any help or suggestion.
Thanks,
Jack
October 31, 2008 at 3:47 am
Yes. There are probably more than a couple of ways of doing it, let me share with you the good-n-old pivot table way; not doing your job but here is the pseudo code.
create temporary table mytable(name, value, extended_status)
insert into mytable(name,value) select distinct(name,value) from your_base_table
populate mytable(extended_status) from your_base_table
select name,value,extended_status from mytable
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.October 31, 2008 at 7:45 pm
jack_hc (10/30/2008)
Hi,I am facing a problem to extract data from a table, the table contains over 41k records, and some of them(>5k) share same column value, now what i am requested is to merge records share same column value.
As a simple output or are they suggesting the colossal mistake of storing the data in the database like that?
--Jeff Moden
Change is inevitable... Change for the better is not.
October 31, 2008 at 10:12 pm
November 1, 2008 at 11:34 am
Um... heh... no, sorry... pointing someone to a Cursor Usage URL is not what I'd call a "good start" even if it has a better answer in the post that followed. 😛
If you want to make the mistake of creating CSV's in a data base, at least do it with some performance in mind...
http://www.sqlservercentral.com/articles/Test+Data/61572/
--Jeff Moden
Change is inevitable... Change for the better is not.
November 3, 2008 at 6:01 am
PaulB (10/31/2008)
Yes. There are probably more than a couple of ways of doing it, let me share with you the good-n-old pivot table way; not doing your job but here is the pseudo code.create temporary table mytable(name, value, extended_status)
insert into mytable(name,value) select distinct(name,value) from your_base_table
populate mytable(extended_status) from your_base_table
select name,value,extended_status from mytable
thank you Paul, i'll try that.
November 3, 2008 at 6:03 am
Jeff Moden (10/31/2008)
jack_hc (10/30/2008)
Hi,I am facing a problem to extract data from a table, the table contains over 41k records, and some of them(>5k) share same column value, now what i am requested is to merge records share same column value.
As a simple output or are they suggesting the colossal mistake of storing the data in the database like that?
just extract data into a excel/csv file would be fine.
November 3, 2008 at 6:17 am
As always I would say do this in your front end application if you want to show it there
or in SQL Server 2005, you can also use
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254
Failing to plan is Planning to fail
November 3, 2008 at 5:58 pm
jack_hc (11/3/2008)
Jeff Moden (10/31/2008)
jack_hc (10/30/2008)
Hi,I am facing a problem to extract data from a table, the table contains over 41k records, and some of them(>5k) share same column value, now what i am requested is to merge records share same column value.
As a simple output or are they suggesting the colossal mistake of storing the data in the database like that?
just extract data into a excel/csv file would be fine.
Then, the STUFF method that Madhivanan provided the link for above should work a treat for the problem.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply