Select one column data in different columns in SQL Server

Question

I have to match two tables, table1 and table2, for one row of table1 I have two or more rows in table 2 and I want to display them in one row.

Now I have the following:

Select
    a.[ID], b.[Description]
From 
    table1 a, table2 b 
Where 
    a.[ID] = b.[ID];

Output:

[ID] | [Description]
-----+--------------
1    | Fee
1    | Domestic Fee
2    | Fee
2    | International Fee

I want to get the following result

[ID] | [Description1] | [Description2]
-----+----------------+---------------
1    | Fee            | Domestic Fee
2    | Fee            | International Fee

Thank you in advance :)

Search for PIVOT. What if there will be more than 2 descriptions? — Lukasz Szozda
– Lukasz Szozda, Commented Aug 8, 2017 at 14:08
Bad habits to kick : using old-style JOINs - that old-style comma-separated list of tables style was replaced with the proper ANSI JOIN syntax in the ANSI-92 SQL Standard (25 years ago) and its use is discouraged — marc_s
– marc_s, Commented Aug 8, 2017 at 14:09
I searched for PIVOT but it needs some aggregate functions, which I don't need. There will be fixed amount of descriptions per on row for table1 — Tvitmsvleli
– Tvitmsvleli, Commented Aug 8, 2017 at 14:14

Kannan Kandasamy · Accepted Answer · 2017-08-08 14:27:23Z

1

If you have multiple descriptions then you can use Pivot as below:

Select * from (
    Select *, RowN = Concat('Description', Row_Number() over (partition by Id order by Id)) from #description ) a
    pivot (max([Description]) for RowN in ([Description1],[Description2])) p

Output as below:

+----+--------------+-------------------+
| Id | Description1 |   Description2    |
+----+--------------+-------------------+
|  1 | Fee          | Domestic Fee      |
|  2 | Fee          | International Fee |
+----+--------------+-------------------+

answered Aug 8, 2017 at 14:27

Kannan Kandasamy

14k3 gold badges28 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ryan B. Over a year ago

You have to make that dynamic sql. What will happen if there's a third Description -- it's not specified in your 'for' clause.

Mat · Accepted Answer · 2017-08-08 14:57:08Z

1

This is my proposal with cross apply operator

data prepartaion:

create table tst_1 (id int, dsc nvarchar(30))

insert into tst_1 (id, dsc)
values (1,'Fee'),(1,'Domestic Fee'),(2,'Fee'),(2,'International Fee')

Next simple select with jon show data that you looking for:

select t1.id, t1.dsc, x.dsc
from tst_1 t1
cross apply ( select row_number() over (order by id) as lp
                                        ,id
                                        ,dsc
                            from tst_1 )x
where x.id = t1.id and x.dsc <> t1.dsc
and lp%2 = 0

tst_1 can be a view based on select ... from you question.

edited Aug 8, 2017 at 14:57

answered Aug 8, 2017 at 14:47

Mat

3232 silver badges12 bronze badges

Comments

theDbGuy · Accepted Answer · 2017-08-08 14:16:56Z

0

Select
    a.[ID],a.[Description], b.[Description]
From 
    table1 a left outer join table2 b 
on
    a.[ID] = b.[ID];``

answered Aug 8, 2017 at 14:16

theDbGuy

9511 gold badge10 silver badges22 bronze badges

3 Comments

Rafael Over a year ago

why LEFT OUTER JOIN and not INNER JOIN ?

Ryan B. Over a year ago

Is there a [description] field in Table 1? I think you're making assumptions.

theDbGuy Over a year ago

@greespark just an assumption.

Kashif Qureshi · Accepted Answer · 2017-08-08 14:40:31Z

0

try this:

select ID, DESCRIPTION1, DESCRIPTION2
from
(
    Select a.[ID]
      ,b.[Description]
      ,'DESCRIPTION' + CAST(ROW_NUMBER() OVER(PARTITION BY a.ID ORDER BY a.ID) AS VARCHAR(255))AS RN
  from table1 a 
  JOIN  table2 b ON  a.[ID] = b.[ID] 
) d
pivot
(
  max([Description])
  for RN in (DESCRIPTION1,DESCRIPTION2)
) piv;

edited Aug 8, 2017 at 14:40

answered Aug 8, 2017 at 14:32

Kashif Qureshi

1,4902 gold badges13 silver badges20 bronze badges

Collectives™ on Stack Overflow

Select one column data in different columns in SQL Server

4 Answers 4

1 Comment

Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related