Convert row into column and generate dynamic columns

Question

Convert row into column and generate dynamic columns

chalam87

Right there with Babe

Points: 771
More actions
January 5, 2016 at 4:54 am

#310591

Hi,
Please help me this query.
Here with i attached expected result in xl format , please find it attachment.
create table #temptable
(
ResourceName varchar(100),
ResourceCode varchar(100),
RecordID varchar(100),
Program varchar(100),
Laboratory varchar(100),
Test_Date varchar(30),
Analyte varchar(50),
DetectedValue varchar(100),
UOM varchar(20),
CorrectiveAction varchar(100),
AnalyteComments varchar(500)
)
insert into #temptable
values
('10000005563 - CK JL BF PEPP JERK MA','10000005563','10000005563-15290-234419-1-1','Alpena (FSQA)','FSQA','10/17/2015','Cook Date','2015-10-17',null,'','')
,('10000005563 - CK JL BF PEPP JERK MA','10000005563','10000005563-15290-234419-1-1','Alpena (FSQA)','FSQA','10/17/2015','House Cook','20B','10','ok','pass')
,('10000007293 - 10OZ WK BF PEPP JERK 1/1','10000007293','10000007293-15304-005446-1-1','Alpena (FSQA)','FSQA','10/30/2015','Bag Easily Opens','Pass','20','ok','fail')
,('10000007293 - 10OZ WK BF PEPP JERK 1/1','10000007293','10000007293-15304-005446-1-1','Alpena (FSQA)','FSQA','10/30/2015','Best By Date','Pass','30','','test')
select * from #temptable
drop table #temptable
--chalam

Viewing 4 posts - 1 through 3 (of 3 total)

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

Lowell SSC Guru Points: 323507 More actions · Answer 1

your test data wasn't readily consumable, you inserted into the temp table without a definition for it.

here's the data as my best guess for the setup portion: the dynamic pivot you need has lots of examples and articles here on SSC

IF OBJECT_ID('tempdb.[dbo].[#temptable]') IS NOT NULL

DROP TABLE [dbo].[#temptable]

SELECT '10000005563 - CK JL BF PEPP JERK MA' AS ResourceName,

'10000005563' AS ResourceCode,

'10000005563-15290-234419-1-1' AS RecordID,

'Alpena (FSQA)' AS Program,

'FSQA' AS Laboratory,

CONVERT(DATE, '10/17/2015') AS Test_Date,

'Cook Date' AS MeasureName,

'2015-10-17' AS MeasureValue,

NULL AS SequenceNumberMaybe,

'' AS SomeStatus,

'' AS PassFail

into #temptable

UNION ALL

SELECT'10000005563 - CK JL BF PEPP JERK MA','10000005563','10000005563-15290-234419-1-1','Alpena (FSQA)','FSQA','10/17/2015','House Cook','20B','10','ok','pass' UNION ALL

SELECT'10000007293 - 10OZ WK BF PEPP JERK 1/1','10000007293','10000007293-15304-005446-1-1','Alpena (FSQA)','FSQA','10/30/2015','Bag Easily Opens','Pass','20','ok','fail' UNION ALL

SELECT'10000007293 - 10OZ WK BF PEPP JERK 1/1','10000007293','10000007293-15304-005446-1-1','Alpena (FSQA)','FSQA','10/30/2015','Best By Date','Pass','30','','test'

SELECT * FROM #temptable

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

chalam87 Right there with Babe Points: 771 More actions · Answer 2

Hi Lowell,

here is my table structure,

create table #temptable

(

ResourceName varchar(100),

ResourceCode varchar(100),

RecordID varchar(100),

Program varchar(100),

Laboratory varchar(100),

Test_Date varchar(30),

Analyte varchar(50),

DetectedValue varchar(100),

UOM varchar(20),

CorrectiveAction varchar(100),

AnalyteComments varchar(500)

)

--chalam

Oskaruth Old Hand Points: 320 More actions · Answer 3

Are you looking for help with the PIVOT code, chalam87? Or did you just need to know what the term was?

If you need help with the PIVOT, we will need to know what your set keys, your pivot keys, and your pivot values...