Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 697
  • Last Modified:

SQL Select same column, same field and return multiple aliases>

Hello,
I am needing to create a table view that will return resutls for 7 different aliases from the same column.
Individual statements work as expected, how can I create a single statement to return all 7 values?
Or is there another approach I need to consider to accomplis this.
'char' fields ar varchar(160 and 'decimal' fields are decimal(21,6).
Thanks for any advice you can provide.

SELECT system_id, job_id, sub_job_id, sequence, free_field_char As [QAAuditSamples]
            FROM jobfreefield
            WHERE system_id = 'ABC' and sequence = 28
SELECT system_id, job_id, sub_job_id, sequence, free_field_decimal As [QAQtyInspected]
            FROM jobfreefield
            WHERE system_id = 'ABC' and sequence = 921
SELECT system_id, job_id, sub_job_id, sequence, free_field_decimal As [QASavesChecked]
            FROM jobfreefield
            WHERE system_id = 'ABC' and sequence = 922
SELECT system_id, job_id, sub_job_id, sequence, free_field_decimal As [QADefectsFound]
            FROM jobfreefield
            WHERE system_id = 'ABC' and sequence = 923
SELECT system_id, job_id, sub_job_id, sequence, free_field_decimal As [QAPiecesDefect]
            FROM jobfreefield
            WHERE system_id = 'ABC' and sequence = 924
SELECT system_id, job_id, sub_job_id, sequence, free_field_decimal As [QADefectCode]
            FROM jobfreefield
            WHERE system_id = 'ABC' and sequence = 925
SELECT system_id, job_id, sub_job_id, sequence, free_field_char As [QAVarianceApprovedBy]
            FROM jobfreefield
            WHERE system_id = 'ABC' and sequence = 926
SELECT system_id, job_id, sub_job_id, sequence, free_field_char AS [QAComments]
            FROM jobfreefield
            WHERE system_id = 'ABC' and sequence = 927
0
okeefemd
Asked:
okeefemd
1 Solution
 
Scott PletcherSenior DBACommented:
Maybe something like this?:

SELECT system_id, job_id, sub_job_id,
    MAX(CASE WHEN sequence =  28 THEN free_field_char END) AS [QAAuditSamples],
    MAX(CASE WHEN sequence = 921 THEN free_field_decimal END) AS [QAQtyInspected],
    MAX(CASE WHEN sequence = 922 THEN free_field_decimal END) AS [QASavesChecked],
    MAX(CASE WHEN sequence = 923 THEN free_field_decimal END) AS [QADefectsFound],
    MAX(CASE WHEN sequence = 924 THEN free_field_decimal END) AS [QAPiecesDefect],
    MAX(CASE WHEN sequence = 925 THEN free_field_decimal END) AS [QADefectCode],
    MAX(CASE WHEN sequence = 926 THEN free_field_char END) AS [QAVarianceApprovedBy],
    MAX(CASE WHEN sequence = 927 THEN free_field_char END) AS [QAComments]
FROM dbo.jobfreefield
WHERE system_id = 'ABC'
GROUP BY
    system_id, job_id, sub_job_id
ORDER BY
    system_id, job_id, sub_job_id
0
 
okeefemdAuthor Commented:
Scott, this returned the results I am looking for.  Excelent!  Thank you very much.
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Tackle projects and never again get stuck behind a technical roadblock.
Join Now