SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Multiple rows to one row
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dfrimmel
Starting Member

2 Posts

Posted - 06/11/2014 :  05:22:08  Show Profile  Reply with Quote
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
Constraint Violating Yak Guru

United Kingdom
439 Posts

Posted - 06/11/2014 :  06:13:37  Show Profile  Reply with Quote
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!
Go to Top of Page

dfrimmel
Starting Member

2 Posts

Posted - 06/11/2014 :  06:54:27  Show Profile  Reply with Quote
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.
Go to Top of Page

sz1
Constraint Violating Yak Guru

United Kingdom
439 Posts

Posted - 06/11/2014 :  07:20:26  Show Profile  Reply with Quote
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!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000