October 19, 2015 at 3:19 pm
Now, one last thing, based on the sample data what is the expected results? This gives us something to check against and allows you to verify both your assumptions and the sample data (can we actually get the results from the sample data).
This part is usually a manual process on your part. The best way to provide it is create a table called something like ExpectedResults and then use INSERT INTO statements to populate the table with the expected results.
October 19, 2015 at 3:26 pm
Thanks for your help though. This is not a homework assignment. I can't post confidential data so I just posted some sample data to get an idea. I think I should post this into another SSC forum because I am not getting any help over here.
Thanks.
October 19, 2015 at 3:33 pm
Here is the sample tables and data:
CREATE TABLE ListOfValues
(
pid int,
ListValues decimal(3,2)
);
CREATE TABLE Person
(
pid int,
name varchar(50),
Age nvarchar(20)
);
INSERT INTO ListOfValues (pid, ListValues)
VALUES (1, 1),
(2, 0.1),
(3, 0.9),
(4, 1.6),
(5, 2.0),
(6, 3.5);
INSERT INTO Person (pid, name, Age)
VALUES (1, 'TestName1', '20 years'),
(2, 'TestName2', '30 years'),
(3, 'TestName3', '40 years'),
(4, 'TestName4', '50 years'),
(5, 'TestName5', '60 years'),
(6, 'TestName6', '18 years');
By using the following query: The result follows after the query.
WITH RowNums AS( -- This is to get values for calculations of the first and last values
SELECT v.pid, v.ListValues,
ROW_NUMBER() OVER(ORDER BY v.pid) rn,
COUNT(*) OVER() rcount
FROM ListOfValues v
INNER JOIN Person p
ON v.pid = p.pid
)
,X AS -- This will calculate the desired values.
(
SELECT
first = MAX( CASE WHEN rn = 1 THEN ListValues END),
last = MAX( CASE WHEN rn = rcount THEN ListValues END),
highest = MAX(ListValues),
lowest = MIN(ListValues)
FROM RowNums
) --Finally, this will "unpivot" the values.
SELECT
description, value
FROM X
CROSS APPLY (VALUES('first', first),('last', last),('highest', highest),('lowest', lowest)) xx(description, value);
Here is the result:
first 1.00
last 3.50
highest3.50
lowest0.10
Now my question is how to include the name and age columns from the Person table in the result set?
Thank you.
October 19, 2015 at 4:00 pm
ITU_dk2012 (10/19/2015)
Thanks for your help though. This is not a homework assignment. I can't post confidential data so I just posted some sample data to get an idea. I think I should post this into another SSC forum because I am not getting any help over here.Thanks.
First, I did not say this was homework I said if we think it is homework. Second, in no way would we want confidential data posted on this sight. That's why we ask for sample data. The data should representative of the problem domain, including if possible any edge cases that you are aware of as well.
Posting this on another forum isn't really going to get you different people trying to help. Most of us monitor the Recent Posts thread that shows questions asked on all forums. Means you'd still probably get the same people trying to help.
October 19, 2015 at 4:08 pm
Ok got it. I hope I get some help over here then. I have already posted the sample data.
Thanks.
October 19, 2015 at 4:13 pm
Here is your answer.
WITH RowNums AS( -- This is to get values for calculations of the first and last values
SELECT v.pid, v.ListValues,
ROW_NUMBER() OVER(ORDER BY v.pid) rn,
COUNT(*) OVER() rcount
FROM dbo.ListOfValues v
INNER JOIN dbo.Person p
ON v.pid = p.pid
)
,X AS -- This will calculate the desired values.
(
SELECT
first = MAX( CASE WHEN rn = 1 THEN ListValues END),
last = MAX( CASE WHEN rn = rcount THEN ListValues END),
highest = MAX(ListValues),
lowest = MIN(ListValues)
FROM RowNums
), Results as ( --Finally, this will "unpivot" the values.
SELECT
description, value
FROM
X
CROSS APPLY (VALUES('first', first),('last', last),('highest', highest),('lowest', lowest)) xx(description, value)
)
select
p.name,
p.Age,
r.value,
r.description
from
Results r
inner join dbo.ListOfValues lov
on (r.value = lov.ListValues)
inner join dbo.Person p
on (lov.pid = p.pid);
October 19, 2015 at 4:14 pm
ITU_dk2012 (10/19/2015)
Ok got it. I hope I get some help over here then. I have already posted the sample data.Thanks.
Yes, yes you did. But you didn't post your expected results.
October 19, 2015 at 6:22 pm
Thank you so much Lynn. I will test this tomorrow and will let you know.
I appreciate your help.
October 19, 2015 at 6:53 pm
Hi Lynn,
Can you please explain your query? I have hard time understanding the whole query specially this part:
CROSS APPLY (VALUES('first', first),('last', last),('highest', highest),('lowest', lowest)) xx(description, value)
Thank you.
October 20, 2015 at 5:46 am
One thing I am noticing with these last bits of sample data posting is it is not the same sample data as you initially posted. You said there was going to be a group of multiple values, giving us the impression it was a string delimited set. I don't know about the others, but I got confused by the change in sample data. Hence the reason why I waited to see how you would respond to the others before posting again.
Here's a Books Online link for the APPLY operator, which includes both the CROSS and the OUTER versions. If you are still confused after reading this link, please post what part of the article confuses you and what you think it means. We'll help you with the explanation at that point.
October 20, 2015 at 6:42 am
Another good reference to understand the code is the article on Table Value Constructors
And here's an example on using APPLY and Table Value Constructors for unpivoting: http://qa.sqlservercentral.com/articles/CROSS+APPLY+VALUES+UNPIVOT/91234/
EDIT: I believe that Paul White does a better job explaining APPLY than Books Online. Check these links for his explanation:
October 20, 2015 at 7:22 am
Your solution works fine on small amount of data but it doesn't work on large quantities of data. I tried it on joining more than 5 tables on more than four thousand rows but I get different result set. For some Persons I get the wrong last value and the highest or maximum value does not show up in the result set. For some I only get the first value but not the last value. Unfortunately I don't have more sample data but I tested on actual confidential data.
Thanks.
WITH RowNums AS( -- This is to get values for calculations of the first and last values
SELECT v.pid, v.ListValues,
ROW_NUMBER() OVER(ORDER BY v.pid) rn,
COUNT(*) OVER() rcount
FROM ListOfValues v
INNER JOIN Person p
ON v.pid = p.pid
)
,X AS -- This will calculate the desired values.
(
SELECT
first = MAX( CASE WHEN rn = 1 THEN ListValues END),
last = MAX( CASE WHEN rn = rcount THEN ListValues END),
highest = MAX(ListValues),
lowest = MIN(ListValues)
FROM RowNums
), Results as--Finally, this will "unpivot" the values.
( SELECT
description, value
FROM X
CROSS APPLY (VALUES('first', first),('last', last),('highest', highest),('lowest', lowest)) xx(description, value)
)
select p.name, p.Age, r.value, r.description
from Results r
inner join ListOfValues lov
on (r.value = lov.ListValues)
inner join Person p
on (lov.pid = p.pid)
October 20, 2015 at 7:30 am
ITU_dk2012 (10/20/2015)
@Lynn,Your solution works fine on small amount of data but it doesn't work on large quantities of data. I tried it on joining more than 5 tables on more than four thousand rows but I get different result set.
Well of course his solution doesn't work. You only gave us the DLL for two tables along with sample data for those two tables. Lynn's solution was based on two tables, not five.
Unfortunately I don't have more sample data but I tested on actual confidential data.
We all work with confidential data. it is a given that if you are a database administrator, some of the data (if not all of it) is confidential. Since you don't have the time to provide the scrubbed versions of all your tables and sample data for all them, plus your expected results, I don't think we can help you. At this point you need to take what was offered and alter it to fit your specific circumstances.
October 20, 2015 at 7:49 am
If you can't create data that accurately represents your problem, then you might not understand your real problem or your data.
We can't help you with that and we can't understand the problem if you're unable to explain it completely and correctly.
Viewing 15 posts - 31 through 45 (of 46 total)
You must be logged in to reply to this topic. Login to reply