CODEWITHSUNDEEP
c#sqldatabasesql-server-2008
kark
kark

Reputation: 4853

Retrieve value from SQL

I am working on SQL in which i need to trigger a values in following situation

My Question is :

I need a query which should retrieve the values from single SQL table, and the Query result will be in more than one rows, Till now not any problem i am facing. now i need to print the results in single row

My Example

I have 4 columns in my table namely AutoArtId, empArtStage, ArtStageCurrStat, PgsCompleted

when i invoke select * from my table where ArtStageCurrStat=S1010 .The result will be in single row, that's fine.

But when i invoke the query like select * from table, it displaying two rows with all record because my table has originally two rows.

Now my question is shall i have a query which should merge the two rows values into single row while retrieving the record from table

Refer my image below

enter image description here

In Above image is the result i got, So ..

Is there any possibility to give a output from

enter image description here

To

enter image description here

(This picture i have designed, i expect this result)

Any Suggestion for this....

Upvotes: 0

Views: 204

Answers (2)

Paul Maxwell
Paul Maxwell

Reputation: 35623

On the basis of what is seen, a generic SQL query like this would "flatten" those rows into a single row (but the 2 PgsCompleted values need different column names).

    select
        AutoArtId
      , empArtStage
      , MIN(ArtStageCurrStat) as ArtStageCurrStat 
      , MIN(PgsCompleted) as PgsCompleted1
      , MAX(PgsCompleted) as PgsCompleted2
    from YourTable
    group by AutoArtId, empArtStage

Some questions emerge aboout more or less than 2 rows, this may help:

    select
        AutoArtId
      , empArtStage
      , MIN(ArtStageCurrStat) as ArtStageCurrStat 
      , MIN(PgsCompleted) as PgsCompleted1
      , MAX(PgsCompleted) as PgsCompleted2
      , count(*) as num_of
    from YourTable
    group by AutoArtId, empArtStage
    having count(*) <> 2

Here is an approach that uses row_number() which is available in SQL 2008. It assumes you have some field(s) in the table that will allow distinction of oldest and newest (here I have used an autonumbered ID) - AND the assumption that it is the oldest and newest that are the relevant record pairs. In this sqlfiddle I dummied up some sample data representing one group of 3 records and another of a single record.

SELECT
        AutoArtId
      , empArtStage
      , MIN(ArtStageCurrStat) AS ArtStageCurrStat
      , MIN(PgsCompleted) AS PgsCompleted1
      , MAX(PgsCompleted) AS PgsCompleted2
FROM (
      SELECT
              AutoArtId
            , empArtStage
            , ArtStageCurrStat
            , PgsCompleted
            , row_number() over (partition BY AutoArtId, empArtStage
                                  ORDER BY ID ASC) AS oldest
            , row_number() over (partition BY AutoArtId, empArtStage
                                  ORDER BY ID DESC) AS newest
      FROM YourTable
      ) AS derived
WHERE oldest = 1 OR newest = 1
GROUP BY
        AutoArtId
      , empArtStage

Upvotes: 2

Joni
Joni

Reputation: 111389

You can use group by and get the two values with MAX and MIN:

SELECT AutoArtId, empArtStage, min(PgsCompleted), max(PgsCompleted) 
FROM table GROUP BY AutoArtId, empArtStage;

Upvotes: 1

Related Questions