March 1, 2016 at 5:42 pm
Hello,
I need to identify on a single row, up 6 identifiers for a single provider. A provider may have any number of provider ID's ( 1 or many )
As an example:
Provider1 Lastname, Firstname, ID1, ID2, ID3, ID4, ID5, ID6
Provider1 Lastname, Firstname, ID7, NULL, NULL, NULL, NULL, NULL
Provider2 Lastname, Firtsname, ID1, ID2, NULL, NULL, NULL, NULL
I don't know if its possible in relational SQL, but I thought I might ask.
create table dbo.provider
(
PROVID VARCHAR(8),
LASTNAME VARCHAR(20),
FIRSTNAME VARCHAR(20)
)
-- provider 123456, with 7 IDS
INSERT INTO dbo.provider VALUES ('1234561A','Provider1', 'Provider1')
INSERT INTO dbo.provider VALUES ('1234561B','Provider1', 'Provider1')
INSERT INTO dbo.provider VALUES ('1234561C','Provider1', 'Provider1')
INSERT INTO dbo.provider VALUES ('1234561D','Provider1', 'Provider1')
INSERT INTO dbo.provider VALUES ('1234561E','Provider1', 'Provider1')
INSERT INTO dbo.provider VALUES ('1234561F','Provider1', 'Provider1')
INSERT INTO dbo.provider VALUES ('1234561G','Provider1', 'Provider1')
-- provider 234567, with 2 IDS
INSERT INTO dbo.provider VALUES ('2345671A','Provider2', 'Provider2')
INSERT INTO dbo.provider VALUES ('2345671B','Provider2', 'Provider2')
-- SELECT left(PROVID,6) as PROVIDER , LASTNAME , FIRSTNAME , PROVID FROM dbo.provider
note: The first 6 characters of the PROVID field is unique, e.g. 123456, OR 234567. The last two characters, a check digit and location reference have no particular order or sequence.
I have investigated the PIVOT command, but to be honest I am baffled by it. I am not counting or summing anything, so um unsure if it can help.
I can do it procedurally ( maybe a cursor into a temporary table, or maybe on the front end scripting language), but I thought there might be a relational SQL solution.
March 1, 2016 at 6:46 pm
You have almost done everything! 🙂
Just uncomment the last statement and add the missing bit:
SELECT left(PROVID,6) as PROVIDER , LASTNAME , FIRSTNAME , PROVID, ROW_NUMBER() OVER (PARTITION BY left(PROVID,6) ORDER BY PROVID) AS ID_No
FROM dbo.provider
Now it should be easy to go with either PIVOT or a cross-tab query.
_____________
Code for TallyGenerator
March 1, 2016 at 6:55 pm
Only thing - for adding rows when you go over 6 ID's per Provider you're gonna need a tally table.
Here I used a Tally table with sequential numbers from 1 to 20 ( up to 120 PROVID per PROVIDER)
SELECT provider, DT.LASTNAME, DT.FIRSTNAME,
MAX(CASE WHEN (ID_no-1)%6 = 0 THEN DT.PROVID ELSE NULL END) ID1,
MAX(CASE WHEN (ID_no-1)%6 = 1 THEN DT.PROVID ELSE NULL END) ID2,
MAX(CASE WHEN (ID_no-1)%6 = 2 THEN DT.PROVID ELSE NULL END) ID3,
MAX(CASE WHEN (ID_no-1)%6 = 3 THEN DT.PROVID ELSE NULL END) ID4,
MAX(CASE WHEN (ID_no-1)%6 = 4 THEN DT.PROVID ELSE NULL END) ID5,
MAX(CASE WHEN (ID_no-1)%6 = 5 THEN DT.PROVID ELSE NULL END) ID6
FROM (
SELECT left(PROVID,6) as PROVIDER , LASTNAME , FIRSTNAME , PROVID, ROW_NUMBER() OVER (PARTITION BY left(PROVID,6) ORDER BY PROVID) AS ID_No
FROM dbo.provider
) DT
INNER JOIN dbo.Tally T ON T.N-1 = (ID_No-1)/6
GROUP BY N, provider, DT.LASTNAME, DT.FIRSTNAME
_____________
Code for TallyGenerator
March 2, 2016 at 6:09 pm
Hi Sergiy,
Thanks very much for your solution.
It's perfect. What a clever way to do it. So very glad I didn't need to use a cursor
March 3, 2016 at 1:52 pm
Sergiy (3/1/2016)
Only thing - for adding rows when you go over 6 ID's per Provider you're gonna need a tally table.Here I used a Tally table with sequential numbers from 1 to 20 ( up to 120 PROVID per PROVIDER)
SELECT provider, DT.LASTNAME, DT.FIRSTNAME,MAX(CASE WHEN (ID_no-1)%6 = 0 THEN DT.PROVID ELSE NULL END) ID1,
MAX(CASE WHEN (ID_no-1)%6 = 1 THEN DT.PROVID ELSE NULL END) ID2,
MAX(CASE WHEN (ID_no-1)%6 = 2 THEN DT.PROVID ELSE NULL END) ID3,
MAX(CASE WHEN (ID_no-1)%6 = 3 THEN DT.PROVID ELSE NULL END) ID4,
MAX(CASE WHEN (ID_no-1)%6 = 4 THEN DT.PROVID ELSE NULL END) ID5,
MAX(CASE WHEN (ID_no-1)%6 = 5 THEN DT.PROVID ELSE NULL END) ID6
FROM (
SELECT left(PROVID,6) as PROVIDER , LASTNAME , FIRSTNAME , PROVID, ROW_NUMBER() OVER (PARTITION BY left(PROVID,6) ORDER BY PROVID) AS ID_No
FROM dbo.provider
) DT
INNER JOIN dbo.Tally T ON T.N-1 = (ID_No-1)/6
GROUP BY N, provider, DT.LASTNAME, DT.FIRSTNAME
I see no reason to join to a Tally table here. You have to calculate (ID_No-1)/6 anyhow, so just use that as your first group by expression.
You can also move the subtraction into your derived table. That way you do it only once rather than seven times.
SELECT provider, DT.LASTNAME, DT.FIRSTNAME,
MAX(CASE WHEN (ID_no)%6 = 0 THEN DT.PROVID ELSE NULL END) ID1,
MAX(CASE WHEN (ID_no)%6 = 1 THEN DT.PROVID ELSE NULL END) ID2,
MAX(CASE WHEN (ID_no)%6 = 2 THEN DT.PROVID ELSE NULL END) ID3,
MAX(CASE WHEN (ID_no)%6 = 3 THEN DT.PROVID ELSE NULL END) ID4,
MAX(CASE WHEN (ID_no)%6 = 4 THEN DT.PROVID ELSE NULL END) ID5,
MAX(CASE WHEN (ID_no)%6 = 5 THEN DT.PROVID ELSE NULL END) ID6
FROM (
SELECT left(PROVID,6) as PROVIDER , LASTNAME , FIRSTNAME , PROVID, ROW_NUMBER() OVER (PARTITION BY left(PROVID,6) ORDER BY PROVID) - 1 AS ID_No
FROM dbo.provider
) DT
GROUP BY (ID_No)/6, provider, DT.LASTNAME, DT.FIRSTNAME
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
March 3, 2016 at 2:04 pm
Scott Thornton-407727 (3/2/2016)
Hi Sergiy,Thanks very much for your solution.
It's perfect. What a clever way to do it. So very glad I didn't need to use a cursor
Glad to help.
You're always welcome.
_____________
Code for TallyGenerator
March 3, 2016 at 2:18 pm
drew.allen (3/3/2016)
I see no reason to join to a Tally table here. You have to calculate (ID_No-1)/6 anyhow, so just use that as your first group by expression.You can also move the subtraction into your derived table. That way you do it only once rather than seven times.
Perfection has no limits! 🙂
Not so sure about moving subtraction.
I's done 9 times (once per row of dbo.provider) regardless of where to put it.
If you're referring to the number of uses in the script - well, I did not type it 6 times anyway. 😉
I typed it once and then did copy-paste, so the typing effort was exactly the same.
If it's about the length of the script - well, we could use shorted aliases for the derived table - it could save more! 😀
_____________
Code for TallyGenerator
March 3, 2016 at 3:27 pm
Sergiy (3/3/2016)
drew.allen (3/3/2016)
I see no reason to join to a Tally table here. You have to calculate (ID_No-1)/6 anyhow, so just use that as your first group by expression.You can also move the subtraction into your derived table. That way you do it only once rather than seven times.
Perfection has no limits! 🙂
Well, introducing unnecessary reads certainly isn't perfection. Admittedly, the number of reads is small, but they're still unnecessary.
Not so sure about moving subtraction.
I's done 9 times (once per row of dbo.provider) regardless of where to put it.
If you're referring to the number of uses in the script - well, I did not type it 6 times anyway. 😉
I typed it once and then did copy-paste, so the typing effort was exactly the same.
If it's about the length of the script - well, we could use shorted aliases for the derived table - it could save more! 😀
No, I'm mostly referring to legibility and comprehension. Subtracting in the derived table creates a zero-based numbering rather than a one-based numbering. Calculations using zero-based numbering are often simpler than those using a one-based numbering, in particular, calculations that involve modulus.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
March 3, 2016 at 6:42 pm
drew.allen (3/3/2016)
Well, introducing unnecessary reads certainly isn't perfection. Admittedly, the number of reads is small, but they're still unnecessary.
By "perfection" I meant reducing unnecessary reads (your code) rather than introducing it (my code).
🙂
No, I'm mostly referring to legibility and comprehension. Subtracting in the derived table creates a zero-based numbering rather than a one-based numbering. Calculations using zero-based numbering are often simpler than those using a one-based numbering, in particular, calculations that involve modulus.
Drew
Subtraction of one creates exactly the same zero-based numbering in both cases, regardless of where it's placed. Computations performed by the machine are absolutely identical.
From computing point of view - there is no difference at all.
The rest is a matter of taste. Like some say - personal preferences. 😉
_____________
Code for TallyGenerator
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply