Multiple rows to one row - 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

SQL Server Development (2000)

Multiple rows to one row

Author

Topic

dfrimmel
Starting Member

2 Posts

Posted - 2014-06-11 : 05:22:08

Hi,
I'm still using SQL Server 2000 and have view created from joining 5 different tables. My problem is that in this view for specific ID I got multiple rows as result since data in one column is different, for example I got:

1 1 1 1 1
1 2 1 1 1
1 3 1 1 1

Since I need this view for mail merging I was wondering if it is possible to have results in this form:

1 1,2,3 1 1 1

Without repeating 3 times? All solved in my view query.

Thanks.

sz1
Aged Yak Warrior

555 Posts

Posted - 2014-06-11 : 06:13:37

You can do a case statement for each column creating a new column for each one.

After sorting your joins

Column1 = Case
When something then do something
end,
Column2 = Case
When something then do something
end,
...

We are the creators of our own reality!

dfrimmel
Starting Member

2 Posts

Posted - 2014-06-11 : 06:54:27

Case is OK, but "When something then do something" part bothers me. For one case/column I got for example 3 rows and I want it all in one row.

sz1
Aged Yak Warrior

555 Posts

Posted - 2014-06-11 : 07:20:26

Then you will nee to aggregate it somehow or concatenate CONCAT( the results set for one column. By using case statements you can create new columns based on the information from the 3 rows thus condensing the 3 down to single/multiple column outputs. Otherwise if you are talking about repeats on the rows you can use a sub query to return the row you want by using row_number() over(partition

Of course without data this is like platting fog.

We are the creators of our own reality!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources