Click here to Skip to main content
15,070,139 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I need a SQL query to transpose below table into columns.

BOM	Lvl	Ref
ABC	0	ABC
ABC	1	WXYZ
ABC	2	b
ABC	2	c
ABC	2	d
ABC	2	e
ABC	2	f
ABC	1	MNOP
ABC	2	k
ABC	2	l
ABC	2	m
ABC	2	n
ABC	2	o


OutPut Like this.

BOM	0	1       2
ABC	ABC	WXYZ	b
ABC	ABC	WXYZ	b
ABC	ABC	WXYZ	c
ABC	ABC	WXYZ	d
ABC	ABC	WXYZ	e
ABC	ABC	WXYZ	f
ABC	ABC	MNOP	k
ABC	ABC	MNOP	k
ABC	ABC	MNOP	l
ABC	ABC	MNOP	m
ABC	ABC	MNOP	n
ABC	ABC	MNOP	o


What I have tried:

I tried with below query but not getting correct output

SELECT D.[BOM],D.Lvl, DS.av as AV, DS.sa as SA, component, d.ref as [Part No] ,F_type,F_Value ,description,[Mat Type]
FROM tbl_CTB D 
INNER JOIN
(
    SELECT [BOM],ref    
         ,MAX(IIF([Lvl] = 0, [Ref],null)) as 0
          ,MAX(IIF([Lvl] = 1, [Ref], null)) as 1
          ,MAX(IIF([Lvl] = 2, [Ref], null)) as 2
    FROM tbl_CTB
    GROUP BY [BOM],ref
) DS ([BOM],ref, 0, 1, 2)
    ON D.[BOM] = DS.[BOM] and D.Ref =DS.ref;
Posted
Updated 23-Dec-20 0:59am
Comments
Richard Deeming 16-Dec-20 4:00am
   
Not clear. There is nothing in your input data to explain why b is associated with ABC/WXYZ and not ABC/MNOP. Nor why you have two records for b and k.
Member 14936854 17-Dec-20 2:01am
   
Input file is in that format and i have to convert it to output file as shown in output table

1 solution

The data that is missing from your "input file" is the column that indicates the hierarchy of the data - you cannot rely on the order that the data appears in the file, you must have an explicit way of expressing who "reports" to whom.

This would work
SQL
declare @ctb table (BOM  varchar(3), Lvl int, Ref varchar(10), MANAGER varchar(10))
insert into @ctb(BOM, Lvl, Ref, MANAGER) values
 ('ABC',	0,	'ABC', NULL)
,('ABC',	1,	'WXYZ', 'ABC')
,('ABC',	2,	'b', 'WXYZ')
,('ABC',	2,	'c', 'WXYZ')
,('ABC',	2,	'd', 'WXYZ')
,('ABC',	2,	'e', 'WXYZ')
,('ABC',	2,	'f', 'WXYZ')
,('ABC',	1,	'MNOP', 'ABC')
,('ABC',	2,	'k', 'MNOP')
,('ABC',	2,	'l', 'MNOP')
,('ABC',	2,	'm', 'MNOP')
,('ABC',	2,	'n', 'MNOP')
,('ABC',	2,	'o', 'MNOP')
I.e.
BOM	Lvl	Ref		MANAGER
ABC	0	ABC		NULL
ABC	1	WXYZ	ABC
ABC	2	b		WXYZ
ABC	2	c		WXYZ
ABC	2	d		WXYZ
ABC	2	e		WXYZ
ABC	2	f		WXYZ
ABC	1	MNOP	ABC
ABC	2	k		MNOP
ABC	2	l		MNOP
ABC	2	m		MNOP
ABC	2	n		MNOP
ABC	2	o		MNOP
You can then use PIVOT[^] to get close to what you need
SQL
select * from
(
    select BOM, Lvl, Ref, MANAGER
    from @ctb
) anch
PIVOT
(
    MAX(MANAGER) FOR Lvl in ([0],[1],[2])
) PVT
which produces the results
BOM	Ref	0		1		2
ABC	ABC	NULL	NULL	NULL
ABC	b	NULL	NULL	WXYZ
ABC	c	NULL	NULL	WXYZ
ABC	d	NULL	NULL	WXYZ
ABC	e	NULL	NULL	WXYZ
ABC	f	NULL	NULL	WXYZ
ABC	k	NULL	NULL	MNOP
ABC	l	NULL	NULL	MNOP
ABC	m	NULL	NULL	MNOP
ABC	MNOP	NULL	ABC	NULL
ABC	n	NULL	NULL	MNOP
ABC	o	NULL	NULL	MNOP
ABC	WXYZ	NULL	ABC	NULL
You can tidy that up a bit by ignoring some of the rows and by repeating one of the columns
SQL
select BOM, BOM, [2], Ref
from 
(
	select * from
	(
		select BOM, Lvl, Ref, MANAGER 
		from @ctb
	) anch
	PIVOT
	(
		MAX(MANAGER) FOR Lvl in ([0],[1],[2])
	) PVT
) as x
WHERE [2] IS NOT NULL
which gives results
BOM	BOM	2		Ref
ABC	ABC	WXYZ	b
ABC	ABC	WXYZ	c
ABC	ABC	WXYZ	d
ABC	ABC	WXYZ	e
ABC	ABC	WXYZ	f
ABC	ABC	MNOP	k
ABC	ABC	MNOP	l
ABC	ABC	MNOP	m
ABC	ABC	MNOP	n
ABC	ABC	MNOP	o
The only difference between these results and the ones you posted as your expected results are the missing duplicated rows that you do not explain (i.e. your expected results are probably wrong)
   
Comments
Maciej Los 23-Dec-20 7:33am
   
5ed!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900