SQL Server two lines in one

Question

Hello and thanks for your attention

I need to convert data from two different lines into one line.

Let s imagine

ORDERS table:

ID    Name    adress   
-------------------- 
01    AA      adr1
02    BB      adr2
03    CC      adr3

LINEORDERS table:

ID    IDorder    IDprofileType     ProfileType      idPers   
------------------------------------------------------------- 
01    01         01                Organizer        0045
01    01         02                Present          0085
01    01         02                Present          0032
01    01         03                Manager          0018

Lets imagine we want as result >

Desired result:

IDorder   NameOrder     OrganizerID           PresentID           ManagerID       Diff
------------------------------------------------------------- 
01        AA            0045                  0085                0018            yes
01        AA            0045                  0032                0018            yes

Diff is yes if managerID and organizerID are different.

How can I get such a result?

Do I need to create T-SQL temporary tables? What would be the lighter?

I have a lot of lines in the table.

Thanks for your help

@ArunKillu The question is about MS SQL Server and **not** MySQL — Ocaso Protal, Oct 25 '12 at 07:00
@ArunKillu Nope, but lots of question about that topic (group_concat in SQL Server) here on SO, e.g. http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-sql-server-2005 ;) — Ocaso Protal, Oct 25 '12 at 07:54
"Diff is yes if managerID and organizerID are different." -> In your desired result they are not different. The only difference is PresentID. — Ocaso Protal, Oct 25 '12 at 11:48

score 0 · Answer 1 · answered Oct 25 '12 at 06:58

0

You can achieve this with subqueries, but I really think that you must do some reengineering here. Cause there will be a lot of subqueries.

answered Oct 25 '12 at 06:58

Darthman

457
4
21

score 0 · Answer 2 · edited May 23 '17 at 12:03

0

If you are using MS SQL Server, you should try PIVOT. Here are some examples: SQL Server: Examples of PIVOTing String data

edited May 23 '17 at 12:03

Community

1
1

answered Oct 25 '12 at 06:59

Pavel Kudinov

405
2
8

SQL Server two lines in one

2 Answers2