March 17, 2014 at 3:41 pm
Thanks Everyone! This is what I am going with:
TRUNCATE TABLE WEBAPPDATA ---Truncate while testing only
INSERT INTO WEBAPPDATA ( FIELDNAME
,FIELDVALUE
,NID
,CID
,PID
,SID )
SELECT
FIELDNAME
,FIELDVALUE
,NID
,CID
,PID
,SID
FROM
OPENQUERY ( COWEB
,'SELECT * FROM UMO_WEBAPP_DATA' ) AS UMODATA --New data
/*normalize it*/
SELECT
SID
,MAX ( CASE
WHEN FIELDNAME = 'FAMILY' THEN FIELDVALUE
ELSE NULL
END ) AS LASTNAME
,MAX ( CASE
WHEN FIELDNAME = 'GIVEN' THEN FIELDVALUE
ELSE NULL
END ) AS FIRSTNAME
,MAX ( CASE
WHEN FIELDNAME = 'generational' THEN FIELDVALUE
ELSE NULL
END ) AS Suffix
,MAX ( CASE
WHEN FIELDNAME = 'title' THEN FIELDVALUE
ELSE NULL
END ) AS Title
,MAX ( CASE
WHEN FIELDNAME = 'Prefer to Be Called (Nickname)' THEN FIELDVALUE
ELSE NULL
END ) AS Nickname
FROM
WEBAPPDATA
GROUP BY
SID
OUTPUT
SID.................LASTNAME.........................FIRSTNAME.................Suffix..............Title.....................Nickname
1179..................Rios...................................Sandra...................Ms.................................................Sandra
I am going to write some logic based on the result set, but getting the sucker flattened out was the big problem (now I have about 65 more attributes to flatten). THANKS! THANKS! THANKS!
Viewing post 16 (of 15 total)
You must be logged in to reply to this topic. Login to reply