Merge MS SQL from 3 records to one

Question

I have this SQL

SELECT 
      [Item No_]
      ,[Type]
      ,[Property]
      ,[Description]
  FROM [Navision4].[dbo].[3S Company A_S$Item Property] WHERE [Item No_] = '138150'

with this result

Item No_    Type    Property    Description
138150  LABEL   TEXTLINE1   Spiralledning
138150  LABEL   TEXTLINE2   RJ9 til RJ9
138150  LABEL   TEXTLINE3   2m

But, i want a result like this on above specific

Item No_ | TextLine1     | TextLine2   | TextLine3
138150   | Spiralledning | RJ0 til RJ9 | 2m

It's that possible somehow? (With 3 selects in one string?)

You want a **Pivot** - https://technet.microsoft.com/en-us/library/ms177410%28v=sql.105%29.aspx?f=255&MSPPError=-2147217396 — Milney, Aug 22 '17 at 12:28
There are many answers about using Pivot and Unpivot already - now you know the name you should find method — Milney, Aug 22 '17 at 12:28

DhruvJoshi · Accepted Answer · 2017-08-22T12:42:48.333

1

you can try query like below

select [Item No_],[TEXTLINE1],[TEXTLINE2],[TEXTLINE3]
from

(

SELECT 
      [Item No_]
      ,[Property]
      ,[Description]
  FROM [Navision4].[dbo].[3S Company A_S$Item Property] WHERE [Item No_] = '138150'
)
src
pivot
(
max(description) for property in ([TEXTLINE1],[TEXTLINE2],[TEXTLINE3])
)
p

See live demo

edited Aug 22 '17 at 12:42

answered Aug 22 '17 at 12:34

DhruvJoshi

17,041
6
41
60

Merge MS SQL from 3 records to one

1 Answers1