sql query statement

[CARLEENHOP]

Hi
I am trying to get the results in the one statement. But i have a where clause that is different for both statements, tables and columns the same though. not sure how to structure this. e.g. select cost from y where x = 1, but i also need to select cost from y where x = 2, so i want to see a column that has cost for 1 and cost for 2 in the same script.
I hope this makes sense.
Thanks

 

[bborissov]

SELECT Cost 
FROM Y
WHERE x IN (1,2)

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[CARLEENHOP]

Thanks, yes I have this part but it then retrieves two records, where as I want one record 2 columns (showing the cost for each), does that make sense?

 

[markros]

If I understand you correctly, you want to PIVOT your data, right?

In other words,

select max(case when x=1 then Cost end) as Cost1, max(case when x=2 then Cost end) as Cost2 from myTable

PluralSight Learning Library

 

[gmmastros]

Even with the pivot query, you should still include the where clause that Boris suggested.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[CARLEENHOP]

Thanks, thats great as I now get a column for each value, however I still get two rows per item, is there anyway of avoiding this?

 

[bborissov]

DECLARE @TEst TABLE (Cost int, X int)

INSERT INTO @Test VALUES (120, 1)
INSERT INTO @Test VALUES (125, 2)
INSERT INTO @Test VALUES (130, 3)
INSERT INTO @Test VALUES (135, 4)
INSERT INTO @Test VALUES (140, 5)

SELECT *
FROM (SELECT * FROM @Test) AS Src
PIVOT
(MAX(Cost) FOR X IN([1],[2])) AS Pvt

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[CARLEENHOP]

OK, that looks a bit complex for me as I dont understand. Thanks anyway, I very much appreciate.

 

[markros]

With my query or PIVOT syntax Boris suggested, you'll get only one row per both items (don't forget, I included MAX).

PluralSight Learning Library

 

[bborissov]

Try markros suggestion. It will give you the same result.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[CARLEENHOP]

I did use Markros but I still received two records per item.

 

[markros]

Post your exact query - if you used it exactly the way I suggested there is no way to get 2 rows.

PluralSight Learning Library

 

[CARLEENHOP]

-- CURRENT SCRIPT TO FIND IMPORTING HANGER COST PROVISIONED

SELECT L.CODE as BRAND, D.CODE AS DEPARTMENT, S.STYLECODE AS STYLE, J.CODE as JOBNUM, P.GLDATE AS RECDATE,
P.XNUM AS RECNUM, P.TOTQ AS RECUNITS, MAX(CASE WHEN BC.LABNUM = 14 THEN BC.COST END) AS FREIGHT, MAX(CASE WHEN BC.LABNUM = 13 THEN BC.COST END) AS DUTY
--, p.totq * bc.cost as provfreight
FROM STYLE S, BOM_STYLE B, BOM_COSTSHT BC, REFCODE L, REFCODE D, PORD P, PORDD PD, REFCODE J
--WHERE STYLECODE = 'WW0403034'
WHERE b.styleidx = s.styleidx
AND B.CODE = 'DESIGN'
AND BC.BSIDX = B.BSIDX

-- 14 is garments and 45 is accessories - script needs to be run twice for each
-- need to also add label = to accessories when running accessories script
--AND BC.LABNUM in(14,13)
AND BC.LABNUM IN (14,13)
-- and L.CODE = 'ACCESSORIES'
AND S.REF1 = L.RCIDX

AND S.REF2 = D.RCIDX
AND P.PORDIDX = PD.PORDIDX
--AND S.STYLEIDX = PD.STYLEIDX
AND P.ACTIVE = 1
AND P.TYP = 2
AND P.REF3 = S.REF6
AND P.REF3 = J.RCIDX
AND PD.STYLEIDX = 5692
AND P.GLDATE > '01/01/2010'
AND BC.COST > 0
GROUP BY S.STYLECODE, BC.COST, L.CODE, D.CODE, J.CODE, P.TOTQ, P.GLDATE, P.XNUM ;

 

[markros]

You have too many fields you're grouping by - can you try reducing them?

PluralSight Learning Library

 

[CARLEENHOP]

It forces to include all my select fields in my grouping?

 

[bborissov]

Sure,
If you want to get the right result you should add all fields that are not involved in aggregate functions in GROUP BY.
So you should decide which of them you need, and what result you need.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[CARLEENHOP]

Hey, I removed the bc.cost field from the group by (which is the one I had used in the select statement as you suggested and called 'duty' or 'freight' etc) and this resolved my issue. YAY thank you so much for all your help!