July 31, 2012 at 9:38 am
Hello All,
I have situation where table(SQL server 2005) is like that,
Orderid cost indicator count
1 10,000 0 2
1 15,000 1 3
I want to arrange this on the basis of indicator in single Row
Orderid cost (0 - indicator) count cost(1-indicator) count
1 10,000 2 15,000 3
Can any body suggest me
Thanks
Rohit
July 31, 2012 at 9:50 am
How about this:
CREATE TABLE ORDERID
(
OrderId Int,
Cost Numeric(9,2),
Indicator int,
[Count] Int
);
INSERT INTO ORDERID VALUES ( 1, 10000, 0, 2 );
INSERT INTO ORDERID VALUES ( 1, 15000, 1, 3 );
SELECT
ZERO.OrderId, ZERO.Cost, ZERO.[Count],
ONE_COST = ONE.Cost, ONE_COUNT = ONE.[Count]
FROM
ORDERID ZERO
INNER JOIN
(
SELECT
OrderId, Cost, [Count]
FROM
ORDERID
WHERE
Indicator = 1
) ONE ON ZERO.OrderId = ONE.OrderId
WHERE
ZERO.Indicator = 0
July 31, 2012 at 10:10 am
Thanks for response, I am getting require result but few row in my table do not have both indicator value like
Orderid cost indicator count
1 10,000 0 2
1 15,000 1 3
2 9000 0 7
3 20000 0 7
3 9000 1 8
Using your query , row of order 2 is ignore in result table.I want to retain all the value
Thanks
Rohit
July 31, 2012 at 10:17 am
Using a LEFT OUTER JOIN will handle that:
SELECT
ZERO.OrderId, ZERO.Cost, ZERO.[Count],
ONE_COST = ISNULL(ONE.Cost, 000.00), ONE_COUNT = ISNULL(ONE.[Count], 000.00)
FROM
ORDERID ZERO
LEFT OUTER JOIN
(
SELECT
OrderId,
Cost, [Count]
FROM
ORDERID
WHERE
Indicator = 1
) ONE ON ZERO.OrderId = ONE.OrderId
WHERE
ZERO.Indicator = 0;
July 31, 2012 at 10:27 am
If you've got both 0 & 1 records missing, use the following:
CREATE TABLE ORDERID
(
OrderId Int,
Cost Numeric(9,2),
Indicator int,
[Count] Int
);
INSERT INTO ORDERID VALUES ( 1, 10000, 0, 2 );
INSERT INTO ORDERID VALUES ( 1, 15000, 1, 3 );
INSERT INTO ORDERID VALUES ( 2, 9000, 0, 7 );
INSERT INTO ORDERID VALUES ( 3, 20000, 0, 7 );
INSERT INTO ORDERID VALUES ( 3, 9000, 1, 8 );
INSERT INTO ORDERID VALUES ( 4, 6000, 1, 5 );
--TRUNCATE TABLE ORDERID
--SELECT * FROM ORDERID;
WITH ZERO AS
(
SELECT
OrderId,
Cost,
[Count]
FROM
ORDERID
WHERE
Indicator = 0
),
ONE AS
(
SELECT
OrderId,
Cost,
[Count]
FROM
ORDERID
WHERE
Indicator = 1
)
SELECT
OrderId = ISNULL(ZERO.OrderId, ONE.OrderId),
Cost = ISNULL(ZERO.Cost, 000.00),
[Count] = ISNULL(ZERO.[Count], 000.00),
ONE_COST = ISNULL(ONE.Cost, 000.00),
ONE_COUNT = ISNULL(ONE.[Count], 000.00)
FROM
ZERO
FULL JOIN ONE ON ZERO.OrderId = ONE.OrderId
ORDER BY
OrderId;
July 31, 2012 at 12:10 pm
Your post is pretty sparse on details but I think you should look into cross tabs (rows into columns). I have two links in my signature about this topic. If you need more detailed help, please read the first link in my signature to learn about best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 31, 2012 at 2:50 pm
Thank you all for quick response.
Regards
Rohit
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply