Query data with manipulation

Question

Query data with manipulation

jack_hc

Old Hand

Points: 344
More actions
October 30, 2008 at 8:08 pm

#124582

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

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 1

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.

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 2

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sayfrend SSCommitted Points: 1733 More actions · Answer 3

Does this helps...

I have written some thing like that earlier... this might not be good per performance wise... but this will give you a start ...

Click here for link [/url]

Hope this helps,

Imran.

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 4

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jack_hc Old Hand Points: 344 More actions · Answer 5

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.

jack_hc Old Hand Points: 344 More actions · Answer 6

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.

Madhivanan-208264 SSCertifiable Points: 7516 More actions · Answer 7

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

Madhivanan

Failing to plan is Planning to fail

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 8

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)