Need help on SQL query

Question

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 15-Dec-20 18:23pm

Member 14936854

Updated 23-Dec-20 0:59am

Add a Solution

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

CHill60 · Answer 1 · 2020-12-23T00:59:00

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)