Select Different Values from Same Column in a Table and Display It Under Different Columns

select different values from same column in a table and display it under different columns

try this

SELECT a.Store_ID,b.total as order_completed,c.total as order_completed FROM ORDER_HISTORY a
LEFT OUTER join (select Store_ID,count(*) as total from ORDER_HISTORY where Status=57 group by Store_ID) b on a.Store_ID=b.Store_ID
LEFT OUTER join (select Store_ID,count(*) as total from ORDER_HISTORY where Status=53 group by Store_ID) c on a.Store_ID=c.Store_ID
group by a.Store_ID

I want to select same column value as different columns name with different conditions from a single table in oracle sql

You don't need all those inner queries, you can do that with a mix of grouping and case expressions

select  EMP_ID,
        max(case when EMPUDF_ID=10012 then EUDFD_VALUE end) AS ADDRESS,
        max(case when EMPUDF_ID=10013 then EUDFD_VALUE end) as CITY,
        max(case when EMPUDF_ID=10014 then EUDFD_VALUE end) as Phone,
        max(case when EMPUDF_ID=10015 then EUDFD_VALUE end) as state,
        max(case when EMPUDF_ID=10016 then EUDFD_VALUE end) as Zip    
from    EMP_UDF_DATA 
group by EMP_ID

SELECT same column AS different columns based on condition

You can use aggregation:

SELECT <other table columns>,
       MAX(CASE WHEN el.month LIKE '2016%' THEN el.score END) AS score2016,
       MAX(CASE WHEN el.month LIKE '2017%' THEN el.score END) AS score2017 
FROM <other table> INNER JOIN 
     elig el
     ON <other table>.memberid = el.memberid
GROUP BY <other table columns>;

mysql - can I select values from the same column and show them in 2 columns result?

You can use case

select title, sum( case side when 'debit' then balance else 0 end ), 
   sum( case side when 'credit' then balance else 0 end )
from accounts 
group by title

select same column twice having different values for single id using mysql

You need mysql pivot table for that. That is turn some columns into row data. The following query will produce what you want:

SELECT
  m.name,
  m.duration,
  MAX(IF(c.type = 'language', c.value, NULL)) AS language,
  MAX(IF(c.type = 'genre', c.value, NULL)) AS genre
FROM movies AS m
INNER JOIN relationship AS r ON m.id = r.movie_id
INNER JOIN category AS c ON r.category_id = c.id
WHERE m.name = 'x'
GROUP BY m.name;

That will produce:

| name | duration | language | genre    |
| x    | 5 mins   | english  | thriller |

See DEMO

Select multiple values from same column based on 2 columns

You have to query for each column separately...

SELECT 
  p.name,
  (SELECT value FROM Attributes a WHERE attr_id=101 AND a.prod_ID=p.ID) AS price,
  (SELECT value FROM Attributes a WHERE attr_id=102 AND a.prod_ID=p.ID) AS taste
FROM Products p

... or ...
If you are using MSSQL 2008 R2 you can use PIVOT:
http://msdn.microsoft.com/en-us/library/ms177410(v=sql.105).aspx

How to select same column of values with different date criterias and display as column1 and column2 in the same query

For SQL Server something like the below will work, the approach should work for any TSQL, may just need to alter the CASE WHEN for different RDMS. Note that you will need to change your field name Date to something else or you will inevitably run into problems.

SELECT Customer,
   sum(CASE WHEN datepart(year, Date) = 2013 THEN Sales ELSE 0 END) 
            As Sales_2013,
   sum(CASE WHEN datepart(year, Date) = 2014 THEN Sales ELSE 0 END) 
            As Sales_2014
FROM Trans
GROUP BY Customer

EDIT

And because you learn something new every day here would be the PIVOT approach, which I know works in SQL Server 2005, don't know about anything else:

SELECT *
FROM
(
    SELECT datepart(year,Date) As SlsYear,Customer,Sales
    FROM Trans
) As Source
PIVOT
(
    sum(Sales)
    FOR SlsYear IN ([2013],[2014])
) As Pvt

Forgive me if this is not quite correct, just learnt about PIVOT now...

EDIT for Separate Tables as per Comment

You should be able to do this by simply using a UNION ALL query.

SELECT *
FROM
(
    SELECT datepart(year,Date) As SlsYear,Customer,Sales
    FROM tbl2013Sales
    UNION ALL
    SELECT datepart(year,Date) As SlsYear,Customer,Sales
    FROM tbl2014Sales
) As Source
PIVOT
(
    sum(Sales)
    FOR SlsYear IN ([2013],[2014])
) As Pvt

Select from same table, different rows, different columns

The answers above still didn't do exactly what I wanted, because those queries returned two rows and/or messed with the column names.

In the end it turned out to be pretty simple:

SELECT * 
FROM (SELECT c1,c2,c3 FROM table WHERE id=1) AS t1,
FROM (SELECT c5,c6 FROM table WHERE id=2) AS t2

it returns one row, containing the values for c1, c2, c3, c5 and c6 with their corresponding column names

Display data from same column in multiple columns without duplicating SQL Server

You need to enumerate the values before you pivot them. Here is one method for getting the results you want:

select max(case when num = 1 then alphabet end) as alpha1,
       max(case when num = 2 then alphabet end) as alpha2,
       max(case when num = 3 then alphabet end) as alpha3       
from (select t.*, row_number() over (partition by num order by alphabet) as seqnum
      from table t
     ) t
group by seqnum;

---

Related Topics