October 18, 2015 at 9:17 pm
You're not exactly following the example by Alan, although, I'm not sure if it's entirely correct.
Here's an option to do it.
CREATE TABLE test_2(
CustomerID int,
TestValues decimal(5,2)
);
INSERT INTO test_2
VALUES
(1,1.00),
(2,2.10),
(3,1.50),
(4,0.80),
(5,0.90),
(6,2.50),
(7,0.10);
WITH RowNums AS( -- This is to get values for calculations of the first and last values
SELECT *,
ROW_NUMBER() OVER(ORDER BY CustomerID) rn,
COUNT(*) OVER() rcount
FROM test_2
)
,X AS -- This will calculate the desired values.
(
SELECT
first = MAX( CASE WHEN rn = 1 THEN TestValues END),
last = MAX( CASE WHEN rn = rcount THEN TestValues END),
highest = MAX(TestValues),
lowest = MIN(TestValues)
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);
GO
DROP TABLE test_2
October 19, 2015 at 10:10 am
Sorry my mistake that I have not posted a clear scenario. Actually I am looking for way to get the first, highest and last value in the list of values for only one customer. I am not looking based on customer id. Let's say for example for one customer there are 10 values. So in just on row how can I grab the first, highest and last value? Sometimes the last value could be null. I am looking in just one row.
Customerid. Values
1. 1, 2.0, 3.0, 0.9, 5.0, 1.5
Thank you.
October 19, 2015 at 11:24 am
ITU_dk2012 (10/19/2015)
Sorry my mistake that I have not posted a clear scenario. Actually I am looking for way to get the first, highest and last value in the list of values for only one customer. I am not looking based on customer id. Let's say for example for one customer there are 10 values. So in just on row how can I grab the first, highest and last value? Sometimes the last value could be null. I am looking in just one row.Customerid. Values
1. 1, 2.0, 3.0, 0.9, 5.0, 1.5
Thank you.
Are the 10 values in a single column or separate columns?
If the same column, are they always comma delimited?
October 19, 2015 at 11:39 am
ITU_dk2012 (10/19/2015)
Sorry my mistake that I have not posted a clear scenario. Actually I am looking for way to get the first, highest and last value in the list of values for only one customer. I am not looking based on customer id. Let's say for example for one customer there are 10 values. So in just on row how can I grab the first, highest and last value? Sometimes the last value could be null. I am looking in just one row.Customerid. Values
1. 1, 2.0, 3.0, 0.9, 5.0, 1.5
Thank you.
When were you expecting to say that those were comma separated values in a single row and column? That's a bad design and you shouldn't keep it like that.
If they really not in that format, you need something to define first and last. A table has no order, it's not an Excel spreadsheet, but a logical representation of data. You need something to order the values to define first and last.
CREATE TABLE test_2(
CustomerID int,
TestValues varchar(8000)
);
INSERT INTO test_2
VALUES
(1,'1, 2.0, 3.0, 0.9, 5.0, 1.5'),
(2,'2.10'),
(3,'1.50'),
(4,'0.80'),
(5,'0.90'),
(6,'2.50'),
(7,'0.10');
WITH RowNums AS( -- This is to get values for calculations of the first and last values
SELECT CustomerID, CAST( Item AS decimal(5,2)) Item, ItemNumber,
COUNT(*) OVER( PARTITION BY CustomerID) rcount
FROM test_2 t
CROSS APPLY dbo.DelimitedSplit8K( t.TestValues, ',') s
)
SELECT CustomerID,
first = MAX( CASE WHEN ItemNumber = 1 THEN Item END),
last = MAX( CASE WHEN ItemNumber = rcount THEN Item END),
highest = MAX(Item),
lowest = MIN(Item)
FROM RowNums
GROUP BY CustomerID;
WITH RowNums AS( -- This is to get values for calculations of the first and last values
SELECT CustomerID, CAST( Item AS decimal(5,2)) Item, ItemNumber,
COUNT(*) OVER( PARTITION BY CustomerID) rcount
FROM test_2 t
CROSS APPLY dbo.DelimitedSplit8K( t.TestValues, ',') s
)
,X AS -- This will calculate the desired values.
(
SELECT CustomerID,
first = MAX( CASE WHEN ItemNumber = 1 THEN Item END),
last = MAX( CASE WHEN ItemNumber = rcount THEN Item END),
highest = MAX(Item),
lowest = MIN(Item)
FROM RowNums
GROUP BY CustomerID
) --Finally, this will "unpivot" the values.
SELECT
CustomerID, description, value
FROM X
CROSS APPLY (VALUES('first', first),('last', last),('highest', highest),('lowest', lowest)) xx(description, value);
GO
DROP TABLE test_2
This assumes a single row per customer with concatenated values. If it's not, please post accurate sample data and what you tried. Otherwise, I might not bother on helping on this thread anymore.
October 19, 2015 at 12:47 pm
The values are in just one column. Any ideas?
Thanks
October 19, 2015 at 12:56 pm
The values are for just one person or customer. For example if you select all the values for a given customer, it should list all value for the customer so the customer id repeats for each value. I need to find the first value, the maximum and the last value in the list.
Thanks
October 19, 2015 at 1:05 pm
ITU_dk2012 (10/19/2015)
The values are for just one person or customer. For example if you select all the values for a given customer, it should list all value for the customer so the customer id repeats for each value. I need to find the first value, the maximum and the last value in the list.Thanks
You're not helping to make this clearer. POST ACCURATE SAMPLE DATA.
I'm not making any more shots in the dark if you're not willing to show some effort.
October 19, 2015 at 1:40 pm
Luis Cazares (10/19/2015)
ITU_dk2012 (10/19/2015)
The values are for just one person or customer. For example if you select all the values for a given customer, it should list all value for the customer so the customer id repeats for each value. I need to find the first value, the maximum and the last value in the list.Thanks
You're not helping to make this clearer. POST ACCURATE SAMPLE DATA.
I'm not making any more shots in the dark if you're not willing to show some effort.
If we had the teacher/instructor's email address, we could ask the person directly. 🙂
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
October 19, 2015 at 1:58 pm
Alvin Ramard (10/19/2015)
Luis Cazares (10/19/2015)
ITU_dk2012 (10/19/2015)
The values are for just one person or customer. For example if you select all the values for a given customer, it should list all value for the customer so the customer id repeats for each value. I need to find the first value, the maximum and the last value in the list.Thanks
You're not helping to make this clearer. POST ACCURATE SAMPLE DATA.
I'm not making any more shots in the dark if you're not willing to show some effort.
If we had the teacher/instructor's email address, we could ask the person directly. 🙂
That would cut through the ambiguity and obfuscation. Well, we could at least hope it would.
October 19, 2015 at 2:00 pm
The key here, is yes we have ideas, but we'd like to see some effort on your part instead of just expecting us to give you an answer that you may not be able to explain how it works.
That does you no good. We want you to learn, not just get an answer.
October 19, 2015 at 2:24 pm
Hello Luis,
Your solution works fine. Now I need pull more columns from another table by joining it with the table which contains the list of values. For example I need to pull name and age from another table and then join with the table which contains the values. I am using the following modified version and will need to pull the columns. I get the correct result but how can I get the extra two column values of p.Name, p.Age in the result set including the first, last and highest?
Thank you.
WITH RowNums AS( -- This is to get values for calculations of the first and last values
SELECT p.Name, p.Age,
ROW_NUMBER() OVER(ORDER BY v.personid) rn,
COUNT(*) OVER() rcount
FROM Values v
INNER JOIN Person p
ON v.personid = p.personid
)
,X AS -- This will calculate the desired values.
(
SELECT
first = MAX( CASE WHEN rn = 1 THEN TestValues END),
last = MAX( CASE WHEN rn = rcount THEN TestValues END),
highest = MAX(TestValues),
lowest = MIN(TestValues)
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);
October 19, 2015 at 2:40 pm
ITU_dk2012 (10/19/2015)
Hello Luis,Your solution works fine. Now I need pull more columns from another table by joining it with the table which contains the list of values. For example I need to pull name and age from another table and then join with the table which contains the values. I am using the following modified version and will need to pull the columns. I get the correct result but how can I get the extra two column values of p.Name, p.Age in the result set including the first, last and highest?
Thank you.
WITH RowNums AS( -- This is to get values for calculations of the first and last values
SELECT p.Name, p.Age,
ROW_NUMBER() OVER(ORDER BY v.personid) rn,
COUNT(*) OVER() rcount
FROM Values v
INNER JOIN Person p
ON v.personid = p.personid
)
,X AS -- This will calculate the desired values.
(
SELECT
first = MAX( CASE WHEN rn = 1 THEN TestValues END),
last = MAX( CASE WHEN rn = rcount THEN TestValues END),
highest = MAX(TestValues),
lowest = MIN(TestValues)
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);
Besides cross posting on multiple threads, another way to lose the interest of volunteers is the piece meal question approach which you are now using.
I'd suggest providing everything Luis has requested and provide us with the full picture of what you are trying to accomplish. If we break it down into pieces for there it is to help in your learning as well as others that may come across this thread with similar issues.
October 19, 2015 at 3:06 pm
@Lynn Pettis, and @Alvin Ramard,
If you both are experts in T-SQL Programming it doesn't mean that you should mock or make fun of other people who are just learning T-SQL.
Here is the example data you asked for.
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');
I appreciate your help.
Thanks
October 19, 2015 at 3:14 pm
ITU_dk2012 (10/19/2015)
@Lynn Pettis, and @Alvin Ramard,If you both are experts in T-SQL Programming it doesn't mean that you should mock or make fun of other people who are just learning T-SQL.
Here is the example data you asked for.
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');
I appreciate your help.
Thanks
We aren't mocking you. We are trying to help you learn. One thing to learn is how to best ask questions on forum manned by volunteers. None of us are paid to provide assistance. We do it because we want to give back to the SQL Server Community. Because we do this on our free time, we don't want to be wasting our time pulling information from people seeking help. If you give us (the ssc community) everything up front you are more likely to get help. If we think we are helping with homework, we will push more back to you initially to get you more involved in the resolution so that you may learn rather than just being given the answers and hoping you will do more to understand what was provided.
Viewing 15 posts - 16 through 30 (of 46 total)
You must be logged in to reply to this topic. Login to reply