April 27, 2010 at 7:01 am
Hi everyone,
I have a table with the following data structure:
office, asset
1, desktop
1, printer
1, printer
2, laptop
2, desktop
3, desktop
3, printer
3, laptop
3, laptop
and im trying to form a query so that i get the following output:
office, desktops, laptops, printers
1, 1, 0, 2
2, 1, 1, 0
3, 1, 1, 2
I've tried the following but to no avail:
SELECT
(
SELECT Office, Count(Asset)
FROM stg_AssetSummary
WHERE Asset= 'Desktop'
Group By Office
)As Desktop,
(
SELECT Count(Asset)
FROM stg_AssetSummary
WHERE Asset= 'Laptop'
Group By Office
)As Laptop,
(
SELECT Count(Asset)
FROM stg_AssetSummary
WHERE Asset= 'Printer'
Group By Office
) As Printer
Any help would be graciously received!
--
I thought what I'd do was, I'd pretend I was one of those deaf-mutes.
April 27, 2010 at 7:09 am
SELECT office,
SUM(CASE WHEN asset = 'desktop' THEN 1 ELSE 0 END) AS [Desktop],
SUM(CASE WHEN asset = 'laptops' THEN 1 ELSE 0 END) AS [Laptops],
SUM(CASE WHEN asset = 'printers' THEN 1 ELSE 0 END) AS [Printers]
FROM table_name_here
GROUP BY office
April 27, 2010 at 7:15 am
Also
;WITH Descktop AS
(
SELECT Office, Count(Asset) AS Desktop
FROM stg_AssetSummary
WHERE Asset= 'Desktop'
Group By Office
), Laptop AS
(
SELECT Count(Asset) AS Laptops
FROM stg_AssetSummary
WHERE Asset= 'Laptop'
Group By Office
), Printer AS
(
SELECT Count(Asset) AS Printers
FROM stg_AssetSummary
WHERE Asset= 'Printer'
Group By Office
)
SELECT D1.Office, COALESCE(D2.Desktop, 0) AS Desktop,
COALESCE(L.Laptops, 0) AS Laptops,
COALESCE(P.Printers, 0) AS Printers
FROM (SELECT DISTINCT Office
FROM table_name)D1
LEFT OUTER JOIN Desktops D2
ON D1.Office = D2.Office
LEFT OUTER JOIN Laptops L
ON D1.Office = L.Office
LEFT OUTER JOIN Printers P
ON P.Office = D1.Office
April 27, 2010 at 7:20 am
Also
SELECT D.Office,
(SELECT COUNT(*)
FROM table_name
WHERE asset = 'desktop'
AND Office = D.Office) AS 'Desktop'
,(SELECT COUNT(*)
FROM table_name
WHERE asset = 'laptop'
AND Office = D.Office) AS 'Laptop'
,(SELECT COUNT(*)
FROM table_name
WHERE asset = 'printer'
AND Office = D.Office) AS 'Pesktop'
FROM (SELECT DISTINCT Office
FROM table_name) D
April 27, 2010 at 7:24 am
Thank you for the quick reply 🙂 SUM (CASE...) was what i was looking for and works like a charm, thanks again!
--
I thought what I'd do was, I'd pretend I was one of those deaf-mutes.
April 27, 2010 at 7:25 am
You are welcome.
Try this also:
SELECT D.Office, ISNULL(D1.d, 0) AS Desktop
,ISNULL(D2.l, 0) AS Laptop
,ISNULL(D3.p, 0) AS Printer
FROM (SELECT DISTINCT Office
FROM table_name) D
OUTER APPLY
(SELECT COUNT(*)
FROM table_name
WHERE asset = 'desktop'
AND Office = D.Office) AS D1(d)
OUTER APPLY
(SELECT COUNT(*)
FROM table_name
WHERE asset = 'laptop'
AND Office = D.Office) AS D2(l)
OUTER APPLY
(SELECT COUNT(*)
FROM table_name
WHERE asset = 'printer'
AND Office = D.Office) AS D3(p)
April 27, 2010 at 7:35 am
Hey lee, you may also want to try this
IF OBJECT_ID('TEMPDB..#OFFICE_ASSETS') IS NOT NULL
DROP TABLE #OFFICE_ASSETS
CREATE TABLE #OFFICE_ASSETS
(
OFFICE_ID INT,
ASSET_NAME VARCHAR(100)
)
INSERT INTO #OFFICE_ASSETS
SELECT 1,'desktop'
UNION ALL
SELECT 1,'printer'
UNION ALL
SELECT 1,'printer'
UNION ALL
SELECT 2,'laptop'
UNION ALL
SELECT 2,'desktop'
UNION ALL
SELECT 3,'desktop'
UNION ALL
SELECT 3,'printer'
UNION ALL
SELECT 3,'laptop'
UNION ALL
SELECT 3,'laptop'
SELECT * FROM #OFFICE_ASSETS
SELECT OFFICE_ID , ISNULL (desktop, 0) desktopS ,ISNULL (printer, 0) printer , ISNULL (laptop, 0)laptop
FROM
(SELECT OFFICE_ID, ASSET_NAME , COUNT(ASSET_NAME) [COUNT] FROM #OFFICE_ASSETS
GROUP BY OFFICE_ID, ASSET_NAME ) PIVOT_TABLE
PIVOT
( MAX(PIVOT_TABLE.[COUNT]) FOR PIVOT_TABLE.ASSET_NAME IN ([desktop],[printer],[laptop]) ) PIVOT_HANDLE
Hope this helps you!
April 27, 2010 at 7:46 am
thank you thank you for all the replies, it just shows me how many different ways there are to accomplish the same thing 🙂
--
I thought what I'd do was, I'd pretend I was one of those deaf-mutes.
April 27, 2010 at 8:25 am
You're welcome Lee!!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply