Interview Question - Query Required

Question

Interview Question - Query Required

vs.satheesh

SSCrazy

Points: 2392
More actions
July 26, 2019 at 7:42 am

#3666267

create table #testtbl(EmpName varchar(50),Item varchar(40))

insert into #testtbl values('ram','XX')
insert into #testtbl values('madhu','XX')
insert into #testtbl values('madhu','YY')
insert into #testtbl values('rahul','XX')
insert into #testtbl values('rahul','YY')
insert into #testtbl values('rahul','ZZ')
select * from #testtbl

I need following Output
EmpName item1 item2 item3
ram xx - --
madhu xx yy -
rahul xx yy zz

In this above output empname and items is dynamic value.not a static values.Please share me query.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99126 More actions · Answer 1

What was your answer first? It's no good us answering your interview questions for you, it's not us sitting the interview.

If you tell us what you think the answer is though, we'll be happy to discuss your answer, and give you other ideas, pointers, etc.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Jonathan AC Roberts SSCoach Points: 18263 More actions · Answer 2

If you knew the items were fixed you could get them back using a PIVOT query:

SELECT *
 FROM #testtbl src
 PIVOT (MAX(Item) FOR Item IN (XX, YY, ZZ)) as piv

As you say they are dynamic you need to generate that SQL from the contents of the table then execute it:

DECLARE @Items nvarchar(MAX) =
  (SELECT STUFF((SELECT DISTINCT ', ' + Item
                   FROM #testtbl 
                  ORDER BY 1
                    FOR XML PATH(''),TYPE).value('.','nvarchar(MAX)'),1,2,''))

DECLARE @Sql nvarchar(MAX) =
'SELECT *
   FROM #testtbl src
  PIVOT (MAX(Item) FOR Item IN (' + @Items + ')) as piv'

EXEC(@Sql)

drew.allen SSC Guru Points: 76944 More actions · Answer 3

Jonathan AC Roberts wrote:

If you knew the items were fixed you could get them back using a PIVOT query:

SELECT *
 FROM #testtbl src
 PIVOT (MAX(Item) FOR Item IN (XX, YY, ZZ)) as piv

As you say they are dynamic you need to generate that SQL from the contents of the table then execute it:

DECLARE @Items nvarchar(MAX) =
  (SELECT STUFF((SELECT DISTINCT ', ' + Item
                   FROM #testtbl 
                  ORDER BY 1
                    FOR XML PATH(''),TYPE).value('.','nvarchar(MAX)'),1,2,''))

DECLARE @Sql nvarchar(MAX) =
'SELECT *
   FROM #testtbl src
  PIVOT (MAX(Item) FOR Item IN (' + @Items + ')) as piv'

EXEC(@Sql)

I'm not convinced that this needs to be dynamic. He's not updating the columns names based on the values, but rather using Item1, Item2, and Item3 regardless of the values, which can be accomplished with a ROW_NUMBER().

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

ScottPletcher SSC Guru Points: 100947 More actions · Answer 4

Like so:

SELECT EmpName,
    MAX(CASE WHEN row_num = 1 THEN Item END) AS item1,
    MAX(CASE WHEN row_num = 2 THEN Item END) AS item2,
    MAX(CASE WHEN row_num = 3 THEN Item END) AS item3,
    MAX(CASE WHEN row_num = 4 THEN Item END) AS item4
FROM (
    SELECT EmpName, Item, ROW_NUMBER() OVER(PARTITION BY EmpName ORDER BY Item) AS row_num
    FROM #testtbl
) AS derived
GROUP BY EmpName

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 5

vs.satheesh wrote:

In this above output empname and items is dynamic value.not a static values.Please share me query.

This is an interview and this is actually pretty basic stuff. So, no... I'm not going to do you the disservice of simply providing you a query. What I will do is give you the opportunity to learn. Please see the following articles and then you solve the given problem. You are, after all, going to have to be able to actually do such things if you get the job.

https://www.sqlservercentral.com/articles/cross-tabs-and-pivots-part-1-%e2%80%93-converting-rows-to-columns-1

https://www.sqlservercentral.com/articles/cross-tabs-and-pivots-part-2-dynamic-cross-tabs

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jonathan AC Roberts SSCoach Points: 18263 More actions · Answer 6

ScottPletcher wrote:

Like so:

SELECT EmpName,
    MAX(CASE WHEN row_num = 1 THEN Item END) AS item1,
    MAX(CASE WHEN row_num = 2 THEN Item END) AS item2,
    MAX(CASE WHEN row_num = 3 THEN Item END) AS item3,
    MAX(CASE WHEN row_num = 4 THEN Item END) AS item4
FROM (
    SELECT EmpName, Item, ROW_NUMBER() OVER(PARTITION BY EmpName ORDER BY Item) AS row_num
    FROM #testtbl
) AS derived
GROUP BY EmpName

That's a good solution, and I think the correct one. One of the differences is that it won't necessarily have the same items in each column so, for example, you could have XX and YY in the same column. Whereas the pivot method ensures that each item code has its own column.