How to do 2 queries and display results in one table?

Transact-SQL
1

Hi All,

I would like to achieve the following:

Customer	Date	Metric	Figure	Metric	Figure
1	        26-Apr	5	…	11	…
2	        26-Apr	5	…	11	…
3	        26-Apr	5	…	11	…
4	        26-Apr	5	…	11	…
5	        26-Apr	5	…	11	…

The 4 first Columns are easy:

SELECT Customer, Date, Metric, MetricFigure
FROM TBL_Customers
WHERE Date  '26-APR-2016' AND MetricFigure = '5'

Then I would like to do the same query wth MetricFigure = '11' and display the values in a column adjacent to the first query.

What is the best solution to do this?

Many Thanks!

2

declare @Test table (Customer int , [Date] Date, Metric int, MetricFigure varchar(50))

insert into @Test
values
(1,'20160426',5,'...')
,(2,'20160426',5,'...')
,(3,'20160426',5,'...')
,(4,'20160426',5,'...')
,(5,'20160426',5,'...')
,(1,'20160426',11,'...')
,(2,'20160426',11,'...')
,(3,'20160426',11,'...')
,(4,'20160426',11,'...')
,(5,'20160426',11,'...')

--original table
select * from @Test

--presumably what you wanted?
Select
a.*,
b.Metric,
b.MetricFigure
from ( select * from @Test where Metric = 5) a
left join (select * from @Test where Metric = 11) b
on
a.Customer = b.Customer
and a.Date = b.date

3 
SELECT  Customer ,
        Date ,
        a.Metric ,
        a.MetricFigure,
        b.Metric ,
        b.MetricFigure
FROM    TBL_Customers AS a
		OUTER APPLY
		(
			SELECT Metric, Figure
			FROM TBL_Customers b
			WHERE
				b.Customer = a.Customer
				AND b.Date = a.Date
				AND b.Metric = 11
		) AS b
WHERE   Date = '26-APR-2016'
        AND MetricFigure = '5'

If the Metrics are always 5 (and 11), you could also use MAX functions which perhaps would be more efficient.

4

another way

select * from @Test a
cross apply @Test t
where
a.Metric = 5
and a.Customer = t.Customer
and a.Metric <> t.Metric

5 
SELECT 
    Customer, Date, 
    MAX(CASE WHEN MetricFigure =  '5' THEN Metric END) AS Metric5, '5' AS MetricFigure5,
    MAX(CASE WHEN MetricFigure = '11' THEN Metric END) AS Metric11, '11' AS MetricFigure11
FROM TBL_Customers
WHERE Date = '26-APR-2016' AND MetricFigure IN ( '5', '11' )
GROUP BY Customer, Date