CODEWITHSUNDEEP
sqlsql-server-2008subquery
wouter
wouter

Reputation: 67

SQL Query - Get Subquery results in Rows rather then Columns

I checked the Search, but none seem to answer this Question.. I expect it to be farely simple though: I have a query that results in two columns, but I need it to result in two rows.. Anyone know how?

this is the query:

               SELECT        (SELECT        COUNT(Id) AS Expr1
                      FROM            Table
                      WHERE        (Description LIKE 'door%')) AS Door,
                         (SELECT        COUNT(Id) AS Expr1
                           FROM            Table AS Table_1
                           WHERE        (Description LIKE 'window%')) AS Window

The result I GET is (of course):

[Door]     [Window]
56         34

The result I'd LIKE to have is the following:

[OPTION]    [NROfRecords]
Door        56
Window      34

Any Ideas? Thanks in advance!

Upvotes: 1

Views: 292

Answers (4)

roman
roman

Reputation: 117370

Solution like this is following DRY principle - you do not repeat Door or Window anywhere. It also easy to add another entities here, so you do not repeat a logic. 

select
    C.description,
    count(*)
from Table1 as t
    inner join (
        select 'door%', 'Door' union all
        select 'window%', 'Window'
    ) as C(pattern, description) on t.description like c.pattern
group by C.description

sql fiddle demo

Upvotes: -1

podiluska
podiluska

Reputation: 51494

select 
    case when description like 'door%' then 'door'
         when description like 'window%' then 'window' 
         else '' 
    end as [desc],
    count(id)
from table
where description like 'door%' or description like 'window%'
group by 
(
    case when description like 'door%' then 'door'
    when description like 'window%' then 'window' else '' end
)

Upvotes: 0

Taryn
Taryn

Reputation: 247680

You can use UNPIVOT, I would advise rewriting the query though to below:

select *
from
(
  SELECT 
    sum(case when Description LIKE 'door%' then 1 else 0 end) Door,
    sum(case when Description LIKE 'window%' then 1 else 0 end) Window
  from Table1
) x
unpivot
(
  NrOfRecords
  for [Option] in (Door,  Window)
) u

See SQL Fiddle with Demo

Upvotes: 2

MatBailie
MatBailie

Reputation: 86706

SELECT 'Door'   AS Option, COUNT(id) FROM table WHERE description LIKE 'door%'
UNION ALL
SELECT 'Window' AS Option, COUNT(id) FROM table WHERE description LIKE 'window%'

OR...

WITH
  filtered AS
(
  SELECT
    CASE WHEN description LIKE 'door%'   THEN 'Door'
         WHEN description LIKE 'window%' THEN 'Window'
                                         ELSE 'Other' END AS option,
    *
  FROM
    yourTable
  WHERE
       description LIKE 'door%'
    OR description LIKE 'window%'
)
SELECT
  option,
  COUNT(id)
FROM
  filtered
GROUP BY
  option

Or...

SELECT
  lookup.option,
  COUNT(id)
FROM
(
  SELECT 'door'   AS option
  UNION ALL
  SELECT 'window' AS option
)
  AS lookup
INNER JOIN
  yourTable
    ON yourTable.description LIKE lookup.option + '%'
GROUP BY
  lookup.option

Upvotes: 1

Related Questions