Need help to display result in TSQL

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Need help to display result in TSQL

Author

Topic

vua_rua
Starting Member

14 Posts

Posted - 2011-12-04 : 06:11:44

Hi everyone,
I have a question and need your help.

I have a table as below

EmpID | MachineID | Date | Value
A 1 2011-01-01 2011-01-01 08:00:00
A 1 2011-01-01 2011-01-01 09:00:00
A 1 2011-01-02 2011-01-02 09:00:00
A 2 2011-01-02 2011-01-02 09:02:00
A 2 2011-01-02 2011-01-02 09:04:00
B 1 2011-01-01 2011-01-01 09:04:00
B 1 2011-01-01 2011-01-01 09:04:00

I want to display result

EmpID | MachineID | 2011-01-01 | 2011-01-02
A 1 2011-01-01 08:00:00 2011-01-02 09:00:00
A 1 2011-01-01 09:00:00
A 2 2011-01-02 09:02:00
A 2 2011-01-02 09:04:00
B 1 2011-01-01 09:04:00
B 1 2011-01-01 09:04:00

I did many way but I can't display as above.

I need your help. Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-12-04 : 06:51:37

take a look at the PIVOT operator

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-04 : 07:17:01

http://beyondrelational.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

vua_rua
Starting Member

14 Posts

Posted - 2011-12-04 : 07:17:58

quote:
Originally posted by khtan

take a look at the PIVOT operator

KH
[spoiler]Time is always against us[/spoiler]

Thank you but SQL 2000 do not support PIVOT function.
I did in SQL 2000.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-04 : 07:19:21

quote:
Originally posted by vua_rua

quote:
Originally posted by khtan

take a look at the PIVOT operator

KH
[spoiler]Time is always against us[/spoiler]

Thank you but SQL 2000 do not support PIVOT function.
I did in SQL 2000.

if its sql 2000 try this

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-12-04 : 07:21:19

[code]
select EmpID , MachineID ,
[2011-01-01] = max(case when Date = '2011-01-01' then Value end),
[2011-01-02] = max(case when Date = '2011-01-02' then Value end)
from a_table
group by EmpID , MachineID
[/code]

KH
[spoiler]Time is always against us[/spoiler]

vua_rua
Starting Member

14 Posts

Posted - 2011-12-04 : 07:32:05

quote:

Originally posted by khtan


select EmpID , MachineID ,
       [2011-01-01] = max(case when Date = '2011-01-01' then Value end),
       [2011-01-02] = max(case when Date = '2011-01-02' then Value end)
from   a_table 
group by EmpID , MachineID

KH
[spoiler]Time is always against us[/spoiler]

I tried, but The data only display as below

EmpID | MachineID | 2011-01-01 | 2011-01-02
A | 1 | 2011-01-01 08:00:00 | 2011-01-02 09:00:00
A | 2 | | 2011-01-02 09:04:00

I need display result as below (by MachineID, Date)
EmpID | MachineID | 2011-01-01 | 2011-01-02
A |1 |2011-01-01 08:00:00 | 2011-01-02 09:00:00
A | 1 |2011-01-01 09:00:00
A | 2 |2011-01-02 09:02:00
A | 2 |2011-01-02 09:04:00
B | 1 |2011-01-01 09:04:00
B | 1 |2011-01-01 09:04:00

Do you have any idea. Thanks so much

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-04 : 07:45:48

[code]
IF OBJECT_ID('tempdb..#Temp') IS NOT NULL
DROP TABLE #Temp

select t.*,
coalesce((select count(*) from table where EmpID = t.EmpID and MachineID = t.MachineID and DATEDIFF(dd,0,Date) = DATEDIFF(dd,0,t.Date) AND Date < t.Date),0) + 1 AS Rn
into #Temp
from table t

SELECT EmpID,MachineID,
MAX(CASE WHEN DATEADD(dd,DATEDIFF(dd,0,Date),0) = '2011-01-01' THEN Value END) AS [2011-01-01],
MAX(CASE WHEN DATEADD(dd,DATEDIFF(dd,0,Date),0) = '2011-01-02' THEN Value END) AS [2011-01-02],
....
FROM #Temp t
GROUP EmpID,MachineID,DATEDIFF(dd,0,Date),Rn
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

vua_rua
Starting Member

14 Posts

Posted - 2011-12-04 : 08:20:38

quote:

Originally posted by visakh16


IF OBJECT_ID('tempdb..#Temp') IS NOT NULL
 DROP TABLE #Temp

select t.*,
coalesce((select count(*) from table where EmpID = t.EmpID and MachineID = t.MachineID and DATEDIFF(dd,0,Date) = DATEDIFF(dd,0,t.Date) AND Date < t.Date),0) + 1 AS Rn
into #Temp
from table t

SELECT EmpID,MachineID,
MAX(CASE WHEN DATEADD(dd,DATEDIFF(dd,0,Date),0) = '2011-01-01' THEN Value END) AS [2011-01-01],
MAX(CASE WHEN DATEADD(dd,DATEDIFF(dd,0,Date),0) = '2011-01-02' THEN Value END) AS [2011-01-02],
....
FROM #Temp t
GROUP EmpID,MachineID,DATEDIFF(dd,0,Date),Rn

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Dear visakh16,
Thank for your support. I tried to use your solution,
but when result display. I saw, some value lost.

MachineID 1 has 2 values in 2011-01-01
MachineID 2 has 2 values in 2011-01-02

I need value will display value in 2 rows as below

EmpID | MachineID | 2011-01-01 | 2011-01-02
A 1 2011-01-01 08:00:00 | 2011-01-02 09:00:00
A 1 2011-01-01 09:00:00 | NULL
A 2 NULL 2011-01-02 09:02:00
A 2 NULL 2011-01-02 09:04:00

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-04 : 08:23:00

i just gave you a stub to start off. you need to put similar conditions for other date values also.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

vua_rua
Starting Member

14 Posts

Posted - 2011-12-04 : 08:38:11

quote:
Originally posted by visakh16

i just gave you a stub to start off. you need to put similar conditions for other date values also.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Thank you.
I tried your solution, but I think your SQL query MAX (CASE...) syntax
made lost value.
I'm trying other way but the value still lost and did not display as I need.

If you have any idea, please let me know.

I'm Sorry I'm not good at English.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-04 : 08:39:43

quote:
Originally posted by vua_rua

quote:
Originally posted by visakh16

i just gave you a stub to start off. you need to put similar conditions for other date values also.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Thank you.
I tried your solution, but I think your SQL query MAX (CASE...) syntax
made lost value.
I'm trying other way but the value still lost and did not display as I need.

If you have any idea, please let me know.

I'm Sorry I'm not good at English.

show your full query

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

vua_rua
Starting Member

14 Posts

Posted - 2011-12-04 : 08:59:05

quote:
Originally posted by visakh16

show your full query

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

I use query base on your query:

select t.*,
coalesce((select count(*) from #tmpMTList1 where EmployeeID = t.EmployeeID and MachineID = t.MachineID and DATEDIFF(dd,0,MTDate) <= DATEDIFF(dd,0,t.MTDate)),0) + 1 AS Rn
into #Temp
from #tmpMTList1 t --My table

SELECT * FROM #Temp

SELECT EmployeeID, MachineID,
MAX(CASE WHEN DATEADD(dd,DATEDIFF(dd,0,MTDate),0) = '2010-06-16 00:00:00' THEN MTTime END) AS [2010-06-16],
MAX(CASE WHEN DATEADD(dd,DATEDIFF(dd,0,MTDate),0) = '2010-06-17 00:00:00' THEN MTTime END) AS [2010-06-17]
FROM #Temp
--GROUP BY EmployeeID,MachineID,DATEDIFF(dd,0,MTDate),Rn

DROP TABLE #Temp

when I group by Date, some case are correct
but some case are not correct.
In one day, I can have value in many MachineID
In one MachineID, i can have value in many Day.
So I did not show result as i want

Thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-04 : 09:01:42

yu've only given two date values alone so any values that hapened in other dates will be lost

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

vua_rua
Starting Member

14 Posts

Posted - 2011-12-04 : 09:34:00

quote:
Originally posted by visakh16

yu've only given two date values alone so any values that hapened in other dates will be lost

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

I don't understand. Can you explain for me?
My problem is:
I should to show detail time for each EmpID in a day
and in a Machine, from date - To date

If EmpID A have 2 times in MachineID 1 in 16th
I must to show detail 2 times in 2 rows and data will display in column 1st day...
The same with 2nd day.

So my result will be displayed as below:

EmpID | MachineID | 2011-01-01 | 2011-01-02
A |1 |2011-01-01 08:00:00 |2011-01-02 09:00:00
A |1 |2011-01-01 09:00:00 |NULL
A |2 |NULL|2011-01-02 09:02:00
A |2 |NULL|2011-01-02 09:04:00

If i use ur solution I only saw:

EmployeeID MachineID 2010-06-16 2010-06-17
F10537 1 2010-06-16 10:09:41.000 NULL
F10537 1 NULL 2010-06-17 02:09:41.000
F10537 6 NULL 2010-06-17 09:09:41.000

Do you have any idea for me?. Thank you so much

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-04 : 09:41:36

your both queries have no relation to each other. One has only employee with ID A whilst other one has some other ID. ALso I dont know how your sample data is so I cant suggest what went wrong with limited information you've provided

see how to post a question for getting quick and accurate results

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

vua_rua
Starting Member

14 Posts

Posted - 2011-12-04 : 09:54:08

quote:
Originally posted by visakh16

your both queries have no relation to each other. One has only employee with ID A whilst other one has some other ID. ALso I dont know how your sample data is so I cant suggest what went wrong with limited information you've provided

see how to post a question for getting quick and accurate results

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

This is my table and data sample

--Table
CREATE TABLE [dbo].[CAN_TmpMealtime](
[AttTime] [datetime] NOT NULL,
[MachineID] [int] NOT NULL,
[EmployeeID] [varchar](20) NOT NULL,
[AttState] [int] NULL,
[Shift] [int] NULL,
[ProcessConvert] [bit] NULL,
[Process] [bit] NULL,
CONSTRAINT [PK_CAN_TmpMealtime] PRIMARY KEY CLUSTERED
(
[AttTime] ASC,
[MachineID] ASC,
[EmployeeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
--Insert data
INSERT INTO CAN_TmpMealtime(AttTime,MachineID,EmployeeID,AttState) VALUES('2010-06-16 08:09:41.000','1','A','1')
INSERT INTO CAN_TmpMealtime(AttTime,MachineID,EmployeeID,AttState) VALUES('2010-06-16 08:10:41.000','1','A','1')
INSERT INTO CAN_TmpMealtime(AttTime,MachineID,EmployeeID,AttState) VALUES('2010-06-17 08:09:41.000','1','A','1')
INSERT INTO CAN_TmpMealtime(AttTime,MachineID,EmployeeID,AttState) VALUES('2010-06-17 02:09:41.000','2','A','1')
INSERT INTO CAN_TmpMealtime(AttTime,MachineID,EmployeeID,AttState) VALUES('2010-06-17 02:09:41.000','2','A','1')
INSERT INTO CAN_TmpMealtime(AttTime,MachineID,EmployeeID,AttState) VALUES('2010-06-16 09:09:41.000','1','B','1')
INSERT INTO CAN_TmpMealtime(AttTime,MachineID,EmployeeID,AttState) VALUES('2010-06-16 08:10:41.000','1','B','1')
INSERT INTO CAN_TmpMealtime(AttTime,MachineID,EmployeeID,AttState) VALUES('2010-06-17 08:10:41.000','2','B','1')

Thank you for your support. Can you give me some idea. I think I wrong in some way???

vua_rua
Starting Member

14 Posts

Posted - 2011-12-05 : 02:24:08

Anyone have an idea??

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-05 : 03:13:55

quote:
Originally posted by vua_rua

Anyone have an idea??

Please post what result you need to get for posted data

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

vua_rua
Starting Member

14 Posts

Posted - 2011-12-05 : 04:26:27

quote:
Originally posted by visakh16

quote:
Originally posted by vua_rua

Anyone have an idea??

Please post what result you need to get for posted data

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

I need to show result as below

EmpID | MachineID | 2010-06-16 | 2010-06-17
A |1 |2010-06-016 08:00:00 |2010-06-17 09:00:00
A |1 |2010-06-016 09:00:00 |NULL
A |2 |NULL|2010-06-17 09:02:00
A |2 |NULL|2010-06-17 09:04:00

I think the row index is important. But I cannot get row index for this case. Because row index will be changed by 3 keys: EmpID, machineID, and Date.

Do you have idea for me?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-05 : 04:49:58

you alone know your rules!
Again your sample output has not much relationship to posted sample data. the date value shown in output is different from date values you posted in data. I cant understand why its so difficult for you to post sample data and output as the link suggests. Unless you do that, I dont think anybody will be able to help you further. Please note that we cant read your mind nor can we see your system, so unless you help us with correct information, we will keep on guessing

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources