April 13, 2007 at 12:34 pm
I have a table with several columns, ClientID, PaySourceID and PaySourceName
There can be up to 3 rows per client to store up to three different Pay Sources.
I need to create a view or dump these in a new table so that each of the paysource fields is in a column in one row for each client.
I.E.
ClientID | PaysourceID1 | PaySourceName1 | PaysourceID2 | PaySourceName2 |
etc...
I am doing this because we are trying to import the data into Omniforms and it needs to see everything in one table.
Any suggestions?
April 13, 2007 at 5:00 pm
-- Prepare sample data
DECLARE
@Sample TABLE (ClientID INT, PaySourceID INT, PaySourceName VARCHAR(20))
INSERT
@Sample
SELECT
100, 1, 'A' UNION ALL
SELECT
100, 4, 'DEFG' UNION ALL
SELECT
101, 2, 'BC' UNION ALL
SELECT
101, 4, 'DEFG' UNION ALL
SELECT
100, 5, 'EFGHI' UNION ALL
SELECT
100, 99, 'xyz'
-- Try 1
SELECT
DISTINCT CAST(s1.ClientID AS VARCHAR) + '|' + (SELECT TOP 3 CAST(s2.PaySourceID AS VARCHAR) + '|' + s2.PaySourceName + '|' FROM @Sample AS s2 WHERE s2.ClientID = s1.ClientID ORDER BY s2.PaySourceID FOR XML PATH('')) AS Omniform
FROM
@Sample AS s1
-- Try 2
SELECT
ClientID,
PaySourceID
,
PaySourceName
FROM
(
SELECT ClientID,
PaySourceID
,
PaySourceName
,
ROW_NUMBER() OVER (PARTITION BY ClientID ORDER BY PaySourceID) AS RecID
FROM @Sample
) AS x
WHERE
RecID BETWEEN 1 AND 3
ORDER
BY ClientID,
RecID
-- Try 3
;
WITH Stage(ClientID, PaySourceID, PaySourceName, RecID)
AS
(
SELECT ClientID,
PaySourceID
,
PaySourceName
,
ROW_NUMBER() OVER (PARTITION BY ClientID ORDER BY PaySourceID) AS RecID
FROM @Sample
)
SELECT
ClientID,
PaySourceID
,
PaySourceName
FROM
Stage
WHERE
RecID BETWEEN 1 AND 3
ORDER
BY ClientID,
RecID
N 56°04'39.16"
E 12°55'05.25"
April 15, 2007 at 7:52 pm
I never heard of Omniforms, but it must be a very poor tool for working with a database if it can't handle a simple one to many relationship.
April 16, 2007 at 10:59 am
"I never heard of Omniforms, but it must be a very poor tool for working with a database if it can't handle a simple one to many relationship."
That is a major understatement, I would sooner gouge my eyeballs out than recommend Omniforms to anyone, on any planet, anywhere.
But...that is what the job entails for me.
Thanks for the script...it's a lot for a noob like me to digest but hopefully it will get me onto the right track.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply