Presenting data horizontally and not vertically

Question

I'm fairly new to SQL, but with this my IT department has asked if I could help out as they are not at full strength.

We have a table called dbo_Xrefs which holds all of our competitors cross references so there is a lot of duplicated data see fig 1, what I need is my data to read like fig 2.

Any help would be much appreciated.

Fig1

PartNo      CompName             CompNo
BC2095      A.B.S.               520831
BC2095      amk                  TCA3135
BC2095      APEC braking         LCA235
BC2095      ATE                  220635
BC2095      ATE                  24.3384-1755.7
BC2095      AUDI                 1K0615423A
BC2095      AUDI                 1K0615423D
BC2095      AUDI                 1K0615423J


Fig2

PartNo  amk     ATE     ATE1            AUDI        AUDI 1      AUDI 2                  
BC2095  TCA3135 220635  24.3384-1755.7  1K0615423A  1K0615423D  1K0615423J

Most people here want formatted text, not images - or, even worse - links to images. — jarlh, Sep 26 '17 at 11:14
This answer can help you: https://stackoverflow.com/questions/46391146/transform-rows-into-columns-ms-sql/46391999#comment79741931_46391999 — Serkan Arslan, Sep 26 '17 at 11:18
Likely that a `PIVOT` would be what you are after but I suspect you will need to dynamically generate it based on the Comp Name values. Can you supply more detail from the Xref table (as formatted text as jarlh says) — SQLBadPanda, Sep 26 '17 at 11:19
Possible duplicate of [Efficiently convert rows to columns in sql server](https://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server) — Alberto Martinez, Sep 26 '17 at 11:32

score 1 · Answer 1 · answered Sep 26 '17 at 12:20

You will need to use row_number() to number each CompName partitioned by PartNo,CompName (and -1 to start from 0) for use with pivot().

I have used isnull(nullif(...,0,'') to turn0`s into empty strings.

For a dynamic pivot using your example data:

declare @cols nvarchar(max);
declare @sql  nvarchar(max);

  select @cols = stuff((
      select distinct 
        ',' + quotename(CompName
            +isnull(nullif(convert(nvarchar(10),row_number() over (
                partition by PartNo,CompName
                order by     CompNo 
            )-1),0),'')
            )
      from Xrefs
      for xml path (''), type).value('.','nvarchar(max)')
    ,1,1,'');

select @sql = '
 select PartNo, ' + @cols + '
 from (
    select 
        PartNo
      , rn=CompName+isnull(nullif(convert(nvarchar(10),row_number() over (
          partition by PartNo,CompName 
          order by     CompNo 
          )-1),0),'''')
      , CompNo
    from Xrefs
      ) as a
 pivot (max([CompNo]) for [rn] in (' + @cols + ') ) p';
 select @sql as CodeGenerated;
 exec sp_executesql @sql;

rextester demo: http://rextester.com/XMXA2432

returns:

+--------+--------+---------+--------------+--------+----------------+------------+------------+------------+
| PartNo | A.B.S. |   amk   | APEC braking |  ATE   |      ATE1      |    AUDI    |   AUDI1    |   AUDI2    |
+--------+--------+---------+--------------+--------+----------------+------------+------------+------------+
| BC2095 | 520831 | TCA3135 | LCA235       | 220635 | 24.3384-1755.7 | 1K0615423A | 1K0615423D | 1K0615423J |
+--------+--------+---------+--------------+--------+----------------+------------+------------+------------+

score 0 · Answer 2 · answered Sep 26 '17 at 11:34

you can try dynamic query like below:

CREATE table source_table (PartNo VARCHAR(10),CompName varchar(100),CompNo varchar(100));
INSERT INTO source_table values
('BC2095','A.B.S','520831'),
('BC2095','aml','TCA3135'),
('BC2095','Apec braking','LCA235');



DECLARE @listCompName VARCHAR(MAX)
SELECT @listCompName= 
     COALESCE(@listCompName+'],[','') + CompName
FROM source_table
SET @listCompName='['+@listCompName+']'
DECLARE @qry VARCHAR(MAX)



SELECT @qry=
'select PartNo,'+@listCompName+' from'+
'('+
  'select '+
    'PartNo,CompName,CompNo '+
  'from source_table'+
')src '+
'pivot'+
'( MAX(CompNo) FOR CompName in ('+@listCompName+') )p'
exec(@qry)

working demo

Presenting data horizontally and not vertically

2 Answers2