CODEWITHSUNDEEP
sqlsql-servert-sql
Trevor Elliot
Trevor Elliot

Reputation: 21

Presenting data horizontally and not vertically

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

Upvotes: 0

Views: 93

Answers (2)

SqlZim
SqlZim

Reputation: 38023

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 |
+--------+--------+---------+--------------+--------+----------------+------------+------------+------------+

Upvotes: 1

DhruvJoshi
DhruvJoshi

Reputation: 17126

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

Upvotes: 0

Related Questions