November 17, 2009 at 12:28 am
Hi All
I need some advise for display data
I have data:
CODE REEL_No
----------------------
C10 1138935111
M10 1138935111
M10 113893511L
C10 113893511L
C10 113893511R
M10 113893511R
E10 1138935125
E10 1139032102
E10 1139081101
I want to display data from that table like the structure below
IF The REEL_No Exist on Code, group by REEL_No
REEL_No C10 M10 E10
=====================================
1138935111 Yes Yes NULL
113893511L Yes Yes NULL
113893511R Yes Yes NULL
1138935125 NULL NULL Yes
1139032102 NULL NULL Yes
1139081101 NULL NULL Yes
SELECT REEL_NO,
(CASE WHEN CODE = 'M10' THEN 'YES' ELSE NULL END) AS M10,
(CASE WHEN CODE = 'C10' THEN 'YES' ELSE NULL END) AS C10,
(CASE WHEN CODE = 'E10' THEN 'YES' ELSE NULL END) AS E10
FROM StockIoData GROUP BY REEL_NO,DATA_CODE
I have test this query but the result is not in accordance with my wishes
😀
November 17, 2009 at 1:02 am
SELECT REEL_NO,
max(CASE WHEN CODE = 'M10' THEN 'YES' ELSE NULL END) AS M10,
max(CASE WHEN CODE = 'C10' THEN 'YES' ELSE NULL END) AS C10,
max(CASE WHEN CODE = 'E10' THEN 'YES' ELSE NULL END) AS E10
FROM StockIoData GROUP BY REEL_NO
November 17, 2009 at 1:19 am
Thank you
it really help me....
🙂
November 17, 2009 at 7:11 pm
now I have a problem with that query,
it takes a long time and my application display request timed out...because I have a huge number of record...
so can you give me some advise to view data in a short time?
thankyou
November 17, 2009 at 8:06 pm
Hi,
How may record you want to display, if it’s for the report purpose, then use from REEL CODE and to REEL CODE and between conditions to reduce the retrieve data . And unless see the full table schema it’s hard to say advice.
November 17, 2009 at 9:08 pm
Sorry my English is bad...:-D
I want display about 150K records and select among 1M records
it's for report purpose
the schema is
CREATE TABLE [dbo].[StockIOData] (
[DATA_CODE] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[REEL_NO] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DIV_CODE] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PART_NO] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[QTY] [int] NOT NULL ,
[ACC_DATE] [datetime] NOT NULL ,
[TRAN_DATE] [datetime] NOT NULL ,
[TRAN_TIME] [datetime] NOT NULL ,
[TRAN_USER] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SEND_DATE] [datetime] NULL ,
[SEND_TIME] [datetime] NULL ,
[SEND_USER] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SEND_FLAG] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RSND_DATE] [datetime] NULL ,
[RSND_TIME] [datetime] NULL ,
[RSND_USER] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RSND_COUNT] [int] NULL ,
[PLACE] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
I have tried the cross tab query that I post yesterday
and I tried this query below but it's not for cross tab data.
I want to compare data which have only one data_Code
e.g :
DATA_CODE REEL_NO
M10 1138935111
E10 1138935111
M10 113893511L
E30 113893511L
M10 113893511R
Z20 113893511R
M10 1138935XXX
E10 1138935125
E30 1139032102
Z20 1139081101
I want to check if "M10" with reel_no "1138935XXX" don't have a pair with E10,E30 or Z20
and
I want to check if E10 with reel_no 1138935125 don't have a pair with M10
I want to check if E30 with reel_no 1139032102 don't have a pair with M10
I want to check if Z20 with reel_no 1139081101 don't have a pair with M10
SELECT dbo.StockIOData.DATA_CODE, dbo.StockIOData.REEL_NO, dbo.StockIOData.PART_NO, dbo.StockIOData.QTY, dbo.StockIOData.ACC_DATE,
dbo.StockIOData.TRAN_DATE , dbo.StockIOData.TRAN_TIME, dbo.StockIOData.TRAN_USER, dbo.StockIOData.SEND_FLAG
FROM dbo.StockIOData INNER JOIN
(SELECT REEL_NO From StockIOData
WHERE (DATA_CODE IN ('C10', 'Z20')) AND (TRAN_DATE BETWEEN '2009-10-01' AND '2009-10-31')
GROUP BY REEL_NO
HAVING (COUNT(REEL_NO) = 1)) A ON dbo.StockIOData.REEL_NO = A.REEL_NO
INNER JOIN
(SELECT REEL_NO From StockIOData
WHERE (DATA_CODE IN ('C10', 'E10')) AND (TRAN_DATE BETWEEN '2009-10-01' AND '2009-10-31')
GROUP BY REEL_NO
HAVING (COUNT(REEL_NO) = 1))B ON dbo.StockIOData.REEL_NO = B.REEL_NO
INNER JOIN
(SELECT REEL_NO From StockIOData
WHERE (DATA_CODE IN ('C10', 'E30')) AND (TRAN_DATE BETWEEN '2009-10-01' AND '2009-10-31')
GROUP BY REEL_NO
HAVING (COUNT(REEL_NO) = 1))C ON dbo.StockIOData.REEL_NO = C.REEL_NO
WHERE(dbo.StockIOData.DATA_CODE IN ('C10', 'Z20','E10','E30')) AND (TRAN_DATE BETWEEN '2009-10-01' AND '2009-10-31')
Thank you All
November 17, 2009 at 10:18 pm
Hi,
This may be retrieved quickly, but not understand in this area,
DATA_CODE REEL_NO
M10 1138935111
E10 1138935111
M10 113893511L
E30 113893511L
M10 113893511R
Z20 113893511R
M10 1138935XXX
E10 1138935125
E30 1139032102
Z20 1139081101
I want to check if M10 with reel_no 1138935XXX don't have a pair with E10,E30 or Z20 and
I want to check if E10 with reel_no 1138935125 don't have a pair with M10
I want to check if E30 with reel_no 1139032102 don't have a pair with M10
I want to check if Z20 with reel_no 1139081101 don't have a pair with M10
Give some more examples.
November 17, 2009 at 11:16 pm
This is the data sample, I just display what I need.
DATA_CODE REEL_NO
M10 1138935111
E10 1138935111
M10 113893511L
E30 113893511L
M10 113893511R
Z20 113893511R
M10 1138935XXX
E10 1138935125
E30 1139032102
Z20 1139081101
I want to check if M10 with reel_no 1138935XXX don't have a pair with E10,E30 or Z20
I want to display
M10 1138935XXX
E10 1138935125
E30 1139032102
Z20 1139081101
because that data have no pair with another on reel_no
thank you
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply