January 3, 2017 at 10:06 am
I have a table with the following structure.
Ind_Id | First_Name | Last_Name | FootBall | Soccer | Baseball | Basketball | Volleyball | Fuseball
The sports columns have 'Y' or 'N' values. I would like to run a query that will pull the First and Last Name along with the sports they play. My vision it that it would look like this:
Name | Sports
Jane Doe | Soccer; Volleyball (given that Soccer and Volleyball have a 'Y' value)
Joe Smith | Football; Baseball; Basketball (given that Football, Baseball and Basketball have a 'Y' value)
Any help would be great!
Thanks
January 3, 2017 at 10:34 am
Joe - Welcome to SSCI don't wan't to shatter your vision but both designs are pretty horrible and both violate the 1st normal form... The one you're trying to get to is far worse than what you're trying to get away from.
January 3, 2017 at 10:53 am
joe 46898 (1/3/2017)
I have a table with the following structure.Ind_Id | First_Name | Last_Name | FootBall | Soccer | Baseball | Basketball | Volleyball | Fuseball
The sports columns have 'Y' or 'N' values. I would like to run a query that will pull the First and Last Name along with the sports they play. My vision it that it would look like this:
Name | Sports
Jane Doe | Soccer; Volleyball (given that Soccer and Volleyball have a 'Y' value)
Joe Smith | Football; Baseball; Basketball (given that Football, Baseball and Basketball have a 'Y' value)
Any help would be great!
Thanks
select name = concat(First_Name, ' ', Last_Name),
Sports = iif(Football = 'Y', 'Football; ','') + iif(Soccer = 'Y', 'Soccer; ','')
etc etc
from table
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 3, 2017 at 11:13 am
That work perfectly. Thanks so much for the help!!
January 3, 2017 at 12:41 pm
Which the optimizer then turned into what I was writing:
SELECT Expr1002 = CASE
WHEN tt.Football = 'Y' THEN
'FOOTBALL; '
ELSE
''
END + CASE
WHEN tt.Baseball = 'Y' THEN
'Baseball; '
ELSE
''
END + CASE
WHEN tt.AmericanFootball = 'Y' THEN
'Not Football'
ELSE
''
END
FROM dbo.TestTable AS tt;
I love it when code simplification... isn't.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 3, 2017 at 12:50 pm
This work great but lets say only Baseball has a 'Y' value.
The Return would be: Baseball;
Is it possible to remove the ';' at the end.
January 3, 2017 at 1:08 pm
I prefer the XML concatenation technique. I also put a leading delimiter instead of a trailing delimiter, because you always know exactly where to find the one to remove (using STUFF).
SELECT First_name + ' ' + Last_name, STUFF((
SELECT '; ' + sport_name
FROM (
VALUES
('Football', football),
('Soccer', soccer),
('Baseball', baseball),
('Basketball', basketball),
('Volleyball', volleyball),
('Foosball', fuseball)
) sports(sportname, participant)
WHERE sports.participant = 'Y'
FOR XML PATH(''), TYPE).value('.', 'VARCHAR(100)'), 1, 2, '')
FROM YourTable
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 3, 2017 at 1:30 pm
Keeping Phil's simple formula:
SELECT name = concat(First_Name, ' ', Last_Name),
Sports = STUFF(iif(Football = 'Y', '; Football','') + iif(Soccer = 'Y', '; Soccer','') + etc, 1, 2, '')
FROM YourTable;
January 3, 2017 at 2:00 pm
Luis Cazares (1/3/2017)
Keeping Phil's simple formula:
SELECT name = concat(First_Name, ' ', Last_Name),
Sports = STUFF(iif(Football = 'Y', '; Football','') + iif(Soccer = 'Y', '; Soccer','') + etc, 1, 2, '')
FROM YourTable;
I would argue that my formula is simpler, because my code normalizes the data before operating on it, whereas Phil's does not. Specifically, when changing from a trailing delimiter to a leading delimiter, I only need to change my code in one place, but Phil's code needs to be updated for every single column. It's also easier to add additional columns to mine.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 3, 2017 at 2:37 pm
drew.allen (1/3/2017)
Luis Cazares (1/3/2017)
Keeping Phil's simple formula:
SELECT name = concat(First_Name, ' ', Last_Name),
Sports = STUFF(iif(Football = 'Y', '; Football','') + iif(Soccer = 'Y', '; Soccer','') + etc, 1, 2, '')
FROM YourTable;
I would argue that my formula is simpler, because my code normalizes the data before operating on it, whereas Phil's does not. Specifically, when changing from a trailing delimiter to a leading delimiter, I only need to change my code in one place, but Phil's code needs to be updated for every single column. It's also easier to add additional columns to mine.
Drew
I'd say that it depends. I'd rather have the data normalized and go for the xml concatenation method. But for multiple columns, I feel that keeping the concatenation explicit is simpler and clearer for everyone.
Different points of view.
January 4, 2017 at 8:38 am
You really need to get a book on basic RDBMS because your approach is completely wrong. You also fail to post DDL; please read the forum rules and learn about basic netiquette.
By definition, a table must have a key; but you did not bother with DDL we have no idea what you think a key might be; now we have to do everything for you from scratch The use of assembly language style bit flags is a really, really bad way to abuse SQL. In the 1st chapter of any book in RDBMS. You will learn that a relational schema is made up of tables, tables are made up of rows, and rows are made up of columns. Columns contain scaler values (this is called the information principle; read Codd's 12 rules which would also be in the 1st part of any book on RDBMS).
CREATE TABLE Players
(player_id CHAR(5) NOT NULL,
first_name VARCHAR(10) NOT NULL,
last_name VARCHAR(10) NOT NULL,
sport_name VARCHAR(15) NOT NULL
CHECK (sport_name IN ('FootBall', 'Soccer', 'Baseball', 'Basketball', 'Volleyball', 'Fuseball)),
PRIMARY KEY (player_id, sport_name)
);
Did you notice that the names of the sports are not attributes? They are values on a nominal scale. One of the things you will need to learn is about scales and measurements, so you can do a valid data model.
What you wanted to do was violate what is called 1st Normal Form; this is the foundation of RDBMS. If you really want to be a non-SQL programmer, then use XML concatenated strings and other totally nonrelational things. But if you want to be a SQL programmer, you will avoid it
SQL is based on a tiered architecture; we only deal with the database layer. If you want to concatenate, format, compute or print things and funny colors then you pass the results set from the database layer to a presentation layer. You never do any of those things in the database layer. Unfortunately, this concept is very difficult for most people who grow up with monolithic architectures (Fortran, COBOL, BASIC, etc.), so you will get kludges that do COBOL style string handling in SQL. While you are learning you can try to be better than this.
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
January 5, 2017 at 5:43 am
CELKO (1/4/2017)
CREATE TABLE Players
(player_id CHAR(5) NOT NULL,
first_name VARCHAR(10) NOT NULL,
last_name VARCHAR(10) NOT NULL,
sport_name VARCHAR(15) NOT NULL
CHECK (sport_name IN ('FootBall', 'Soccer', 'Baseball', 'Basketball', 'Volleyball', 'Fuseball)),
PRIMARY KEY (player_id, sport_name)
);
Daring to disagree with Mr. Celko (perhaps "Fools rush in ...")
This table is not normalized either. Name data for a person who played multiple sports would need to be stored multiple times.
It really needs a Sport table (storing an ID and sport name) and then a PlayerSport table that does the many-to-many join.
January 5, 2017 at 11:27 am
You are right. Let us post a correction i just got in a hurry. My bad.
CREATE TABLE Players
(player_id CHAR(5) NOT NULL PRIMARY KEY,
first_name VARCHAR(10) NOT NULL,
last_name VARCHAR(10) NOT NULL);
CREATE TABLE Sports
(player_id CHAR(5) NOT NULL
REFERENCES Players (player_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
sport_name VARCHAR(15) NOT NULL
CHECK (sport_name IN ('Football', 'Soccer', 'Baseball', 'Basketball', 'Volleyball', 'Fuseball’)),
PRIMARY KEY (player_id, sport_name)
);
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
January 5, 2017 at 11:31 am
Fuseball sounds like it could go off with a bang.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 5, 2017 at 11:43 am
CELKO (1/5/2017)
You are right. Let us post a correction i just got in a hurry. My bad.CREATE TABLE Players
(player_id CHAR(5) NOT NULL PRIMARY KEY,
first_name VARCHAR(10) NOT NULL,
last_name VARCHAR(10) NOT NULL);
CREATE TABLE Sports
(player_id CHAR(5) NOT NULL
REFERENCES Players (player_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
sport_name VARCHAR(15) NOT NULL
CHECK (sport_name IN ('Football', 'Soccer', 'Baseball', 'Basketball', 'Volleyball', 'Fuseball’)),
PRIMARY KEY (player_id, sport_name)
);
Take your time. You're not quite there yet. Having a new sport shouldn't require a DDL change. Getting a list of available sports shouldn't need to remove duplicates.
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply