Populate rows with column values, but not a pivot!

Question

Populate rows with column values, but not a pivot!

hilliara7h

Grasshopper

Points: 23
More actions
October 22, 2009 at 11:14 pm

#227410

I have been charged with getting a legacy suite of client apps to play nice with a new database. I chose to do this by recreating the 'old' stored procs they were using on the new db, massaging the new data into the same form and format as those old procs returned. Simple! Mostly it has been. Then I ran aground and I'm still searching for Gilligan or the Skipper (given up on Ginger).
The proc in question returns rows from a single table. The table contains a set of parameter fields (24 of em) and their values for a particular type of device. Each device has one row of parameters. In the new database, the data is similar, but it is stored 'vertically' in one guid and two varchar columns, the first being a ref id for the device, the second being a 'parameter name' and the third being a 'parameter value'. In some cases the parameter names equate to the column names in the old system and in other cases they don't. The parameter values equate to row values. However, the new table contains 'parameters' for all kinds of devices, not just the one device the old table defined.
The values I can handle by Convert / Cast. What I can't figure out is how to extract the parameter names as columns in a new temp table and then pop the table with param values. Which I would then convert as appropriate in the select returning data.
Does anyone have a neat way to do this, or should I continue my search and perhaps find The Professor and Maryanne?
Thanks for any time spent, life's short especially for the Minnow.
>a

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

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

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 1

It's easy and I found Ginger...

See the first link in my signature below to provide a CREATE TABLE statement and some sample data so we can show you how.

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

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 2

I'll second Jeff's request. Long involved verbal descriptions can't take the place of sample schema, a script to populate sample data, and a visual of what the expected result would be like.

Help us help you. 🙂

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

hilliara7h Grasshopper Points: 23 More actions · Answer 3

Thank you, Gentlement. Received and Understood. The following code illustrates the issue. This works, but there has to be a better & more elegant way to do this...

To recap, I am trying to transform the 'vertical' data in #newtable into 'horizontal' data in #oldtable.

-----------------------cut!

IF OBJECT_ID('TempDB..#newtable','U') IS NOT NULL

DROP TABLE #newtable

IF OBJECT_ID('TempDB..#oldtable','U') IS NOT NULL

DROP TABLE #oldtable

--This is the 'new' source data table...

CREATE TABLE #newtable ( DevId int ,

ParamName nvarchar(15),

ParamVal nvarchar(50)

)

--This represents how we want to return data to the celler. I had to set defaults on two fields so that I could make the CASE work later...

CREATE TABLE #oldtable ( DevId int ,

AlarmName nvarchar(30),

TriggerOn tinyint NOT NULL DEFAULT 0,

OnTime int,

TriggerOff tinyint NOT NULL DEFAULT 0,

OffTime int

)

-- there are three 'devices', two of the same type with different param values

-- and one different type with different params

INSERT INTO #newtable (DevId, ParamName, ParamVal)

SELECT 1,'TRIGON','ON'

UNION ALL SELECT 1,'TRIGTIMEON','8000'

UNION ALL SELECT 1,'TRIGOFF','OFF'

UNION ALL SELECT 1,'ALARMTITLE','Shes Gonna Blow!'

UNION ALL SELECT 2,'TRIGON','OFF'

UNION ALL SELECT 2,'TRIGTIMEOFF','250'

UNION ALL SELECT 2,'TRIGOFF','ON'

UNION ALL SELECT 2,'ALARMTITLE','Aye Aye Skipper'

UNION ALL SELECT 3,'TOURLENGTH','Three Hour'

UNION ALL SELECT 3,'PASSENGERCOUNT','5'

UNION ALL SELECT 3,'ISLEPROFILE','UNCHARTED'

-- show our source data

select * from #newtable

-- Start conversion by getting the IDs of devices that are of the correct 'type' into the 'old' table

INSERT INTO #oldtable([DevId]) SELECT DISTINCT DevId from #newtable n where n.ParamName = 'ALARMTITLE'

-- then my plan was to use a case statement to set the various field values. But that didn't work. The following 'works'

-- but is pretty clumsy and labour intensive. What I would like to do is format the following as

-- a single update statement.... If I could have figured how to do a case on the source field and then an update on the dest table it

-- would be easy. But I am not that smart.

UPDATE #oldtable SET AlarmName = n.ParamVal from #oldtable o, #newtable n where n.DevId = o.DevId AND n.ParamName = 'ALARMTITLE'

UPDATE #oldtable SET OnTime = CONVERT(int,n.ParamVal) from #oldtable o, #newtable n where n.DevId = o.DevId AND n.ParamName = 'TRIGTIMEON'

UPDATE #oldtable SET OffTime = CONVERT(int,n.ParamVal) from #oldtable o, #newtable n where n.DevId = o.DevId AND n.ParamName = 'TRIGTIMEOFF'

UPDATE #oldtable SET TriggerOn =

CASE WHEN (n.ParamVal = 'ON') THEN TriggerOn + 1

WHEN (n.ParamVal = 'OFF') THEN TriggerOn * 0

END

from #oldtable o, #newtable n where n.DevId = o.DevId AND n.ParamName = 'TRIGON'

UPDATE #oldtable SET TriggerOff =

CASE WHEN (n.ParamVal = 'ON') THEN TriggerOff + 1

WHEN (n.ParamVal = 'OFF') THEN TriggerOff * 0

END

from #oldtable o, #newtable n where n.DevId = o.DevId AND n.ParamName = 'TRIGOFF'

select DevID, AlarmName,TriggerOn, OnTime, TriggerOff, OffTime from #oldtable

----------------------cut!

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 4

Thank you very much for taking the time to set up the problem so nicely. While I am having to guess at your expected output from the sample data supplied, I believe you are looking for something like this:

select devid

,max(case when ParamName = 'ALARMTITLE' then ParamVal else null end) as AlarmName

,max(case when ParamName = 'TRIGON' then ParamVal else null end) as TriggerOn

,max(case when ParamName = 'TRIGTIMEON' then ParamVal else null end) as OnTime

,max(case when ParamName = 'TRIGOFF' then ParamVal else null end) as TriggerOff

,max(case when ParamName = 'TRIGTIMEOFF' then ParamVal else null end) as OffTime

from #newtable

group by devid

having sum(case when paramName = 'Alarmtitle' then 1 else 0 end) > 0

The above query could be wrapped in a CTE or subquery and joined to your #oldtable for the update, if the query isn't adequate by itself.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

hilliara7h Grasshopper Points: 23 More actions · Answer 5

Bob, thank you so much for taking the time to show me exactly what I was hoping for. Now if we could only find out what happened to Luvvy!

cz

>a

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 6

The "cannibalism" episode from the last season of Gilligan's Island was banned by the sensors.

It was a kindler, gentler time.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills