Renaming fields and combining them into one column

  • 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

  • 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.

  • 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

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • That work perfectly. Thanks so much for the help!!

  • 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

  • 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.

  • 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

  • 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;

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • 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

  • 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.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • 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

  • 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.

  • 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

  • Fuseball sounds like it could go off with a bang.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • 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.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

Viewing 15 posts - 1 through 15 (of 22 total)

You must be logged in to reply to this topic. Login to reply