Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2017
    Posts
    2

    Angry Unanswered: Query Help - SQL

    Trying to query to display only ROWS with a YES value & list what PC# has a yes value. Not working & ask for your help, please.

    SQL syntax:
    <code>
    --------------------------------
    Select from * FROM tbl_PC
    WHERE Function='Yes'
    --------------------------------
    </code>

    Output shows this - not what I wanted

    Function PC1 PC2 PC3 PC4 PC5 PC6
    copy No Yes No Yes No Yes
    paste No Yes No Yes No Yes No
    delete No No Yes Yes No Yes
    backup Yes Yes No No Yes No
    edit No Yes Yes No Yes Yes
    new No No No Yes No No

  2. #2
    Join Date
    Oct 2014
    Posts
    291
    Provided Answers: 7
    Can you post your table and a few rows of data?

    You have columns in the table called PC1, PC2, PC3?

    Function = 'Yes' is shouldn't pull back anything, the column has copy, paste etc.....
    Last edited by VLOOKUP; 04-23-17 at 19:54.
    70-461 SQL Certified.

  3. #3
    Join Date
    Apr 2017
    Posts
    2
    I want the Query to return results showing me those different functions that have a Yes in the table, but I want those yes in output to list the PC#... So the query should show: Copy (function) has two PC's, PC1 & PC3. Similar, query should show that the Backup function lists PC1, PC2, PC3, PC4, PC5 and so on... Does this help? See Photo to better understand what I am attempting to do. Thanks!

    Click image for larger version. 

Name:	query pc.jpg 
Views:	10 
Size:	91.7 KB 
ID:	17340

  4. #4
    Join Date
    Oct 2014
    Posts
    291
    Provided Answers: 7
    I'm not sure if you have dozens of columns or what, if you just have the 6 then here you go.


    WITH GROUPS

    AS

    (

    SELECT [Function]
    ,CASE WHEN PC1 = 'Yes' THEN 'PC1' ELSE '' END AS COMPUTER
    FROM tbl_PC

    UNION ALL

    SELECT [Function]
    ,CASE WHEN PC2 = 'Yes' THEN 'PC2' ELSE '' END AS COMPUTER
    FROM tbl_PC

    UNION ALL

    SELECT [Function]
    ,CASE WHEN PC3 = 'Yes' THEN 'PC3' ELSE '' END AS COMPUTER
    FROMtbl_PC

    UNION ALL


    SELECT [Function]
    ,CASE WHEN PC4 = 'Yes' THEN 'PC4' ELSE '' END AS COMPUTER
    FROM tbl_PC


    UNION ALL

    SELECT [Function]
    ,CASE WHEN PC5 = 'Yes' THEN 'PC5' ELSE '' END AS COMPUTER
    FROM tbl_PC

    UNION ALL

    SELECT [Function]
    ,CASE WHEN PC = 'Yes' THEN 'PC6' ELSE '' END AS COMPUTER
    FROM tbl_PC

    )


    SELECT

    [Function]
    ,ISNULL([PC1],'') AS [1]
    ,ISNULL([PC2],'') AS [2]
    ,ISNULL([PC3],'') AS [3]
    ,ISNULL([PC4],'') AS [4]
    ,ISNULL([PC5],'') AS [5]
    ,ISNULL([PC6],'') AS [6]

    FROM GROUPS
    PIVOT
    (
    MAX([COMPUTER])
    FOR COMPUTER IN ([PC1], [PC2], [PC3], [PC4], [PC5],[PC6])

    ) AS ALL_I_GOT
    70-461 SQL Certified.

  5. #5
    Join Date
    Mar 2015
    Posts
    61
    Provided Answers: 4
    FUNCTION is a Key word for SQL. I always prefer to avoid using keywords in my queries as they can cause unintended results. Please mark your question as answered once you have a acceptable answer. Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •