July 1, 2010 at 11:15 am
Hi all,
I have to design a package to load the data from a table into a destination table converting the actions given in a every row for that tranid to columns as shown in the sample here....
the output for this data given should be like get only one row for tranid =306 ,get first empno = 229, and all actions there(based on no. of rows in src) two actions as IHHE CEMP in action columns
any help on this...
CREATE TABLE [dbo].[test](
[tran_id] [nvarchar](14) NULL,
[type] [nvarchar](3) NULL,
[emp] [nvarchar](5) NULL,
[action] [nvarchar](4) NULL
)
select * from test
INSERT INTO test ( tran_id,tran_id,type,emp,action) VALUES ( '306', '306', 'PRB', '229', 'IHHE')
INSERT INTO test ( tran_id,tran_id,type,emp,action) VALUES ( '306', '306', 'PRB', '229', 'CEMP')
INSERT INTO test ( tran_id,tran_id,type,emp,action) VALUES ( '306', '306', 'PYN', '885', 'DHKP')
INSERT INTO test ( tran_id,tran_id,type,emp,action) VALUES ( '306', '306', 'PYN', '885', 'INPH')
INSERT INTO test ( tran_id,tran_id,type,emp,action) VALUES ( '314', '314', 'PRB', '881', 'CEMP')
INSERT INTO test ( tran_id,tran_id,type,emp,action) VALUES ( '314', '314', 'PYA', '881', 'INPH')
INSERT INTO test ( tran_id,tran_id,type,emp,action) VALUES ( '316', '316', 'PYN', '338', 'CEMP')
INSERT INTO test ( tran_id,tran_id,type,emp,action) VALUES ( '320', '320', 'MLA', '877', 'CEMP')
INSERT INTO test ( tran_id,tran_id,type,emp,action) VALUES ( '324', '324', 'PYA', '429', 'DHKP')
INSERT INTO test ( tran_id,tran_id,type,emp,action) VALUES ( '334', '334', 'PYA', '906', 'DHKP')
INSERT INTO test ( tran_id,tran_id,type,emp,action) VALUES ( '334', '334', 'CBK', '906', 'CEMP')
INSERT INTO test ( tran_id,tran_id,type,emp,action) VALUES ( '346', '346', 'SHD', '630', 'GECD')
INSERT INTO test ( tran_id,tran_id,type,emp,action) VALUES ( '360', '360', 'PYN', '669', 'CANC')
INSERT INTO test ( tran_id,tran_id,type,emp,action) VALUES ( '360', '360', 'RAE', '669', 'GMVD')
INSERT INTO test ( tran_id,tran_id,type,emp,action) VALUES ( '362', '362', 'PYA', '187', 'DHKP')
INSERT INTO test ( tran_id,tran_id,type,emp,action) VALUES ( '362', '362', 'PYA', '187', 'INPH')
INSERT INTO test ( tran_id,tran_id,type,emp,action) VALUES ( '362', '362', 'PYA', '187', 'DHKP')
INSERT INTO test ( tran_id,tran_id,type,emp,action) VALUES ( '362', '362', 'PYA', '187', 'INPH')
INSERT INTO test ( tran_id,tran_id,type,emp,action) VALUES ( '306', '306', 'PYN', '707', 'DHKP')
INSERT INTO test ( tran_id,tran_id,type,emp,action) VALUES ( '306', '306', 'PYN', '707', 'INPH')
Thanks [/font]
July 1, 2010 at 11:48 am
July 1, 2010 at 12:18 pm
Let me put it like this...
from the source i may get more than one row for a tran id but I need to load the destination based on tran id coming in sequence...
the output of this data should be somemthing like this:
tran id empno action1 action 2 action3 action 4
306 229 IHHE CEMP DHKP INPH
314 881 CEMP INPH
316 338 CEMP
320 877 CEMP
324 429 DHKP DHKP CEMP
..
..
306 707 DHKP INPH
actually i have to create one row for every incoming tranid and take the first empno and put it then I have to go thourgh all the rows and until the tranid changes i have to get the action and put them in separate columns making 4 rows for trainid 306 to only one row like this:
306 PRB229IHHE
306 PRB229CEMP
306 PYN229DHKP
306 PYN229INPH
tran id empno action1 action 2 action3 action 4
306 229 IHHE CEMP DHKP INPH
i hope it is clear...thanks
Thanks [/font]
July 1, 2010 at 12:21 pm
a pivot table should work for you. Try this
declare @test-2 table(
[tran_id] [nvarchar](14) NULL,
[date] [nvarchar](12) NULL,
[type] [nvarchar](3) NULL,
[emp] [nvarchar](5) NULL,
[action] [nvarchar](4) NULL,
[action_date] [nvarchar](12) NULL
)
INSERT INTO @test-2 ( tran_id,date,type,emp,action,action_date) VALUES ( '306', 'Jun 29 2010 ', 'PRB', '229', 'IHHE', 'Jul 1 2010 ')
INSERT INTO @test-2 ( tran_id,date,type,emp,action,action_date) VALUES ( '306', 'Jun 29 2010 ', 'PRB', '229', 'CEMP', 'Jul 1 2010 ')
INSERT INTO @test-2 ( tran_id,date,type,emp,action,action_date) VALUES ( '306', 'Jun 29 2010 ', 'PYN', '885', 'DHKP', 'Jul 1 2010 ')
INSERT INTO @test-2 ( tran_id,date,type,emp,action,action_date) VALUES ( '306', 'Jun 29 2010 ', 'PYN', '885', 'INPH', 'Jul 7 2010 ')
INSERT INTO @test-2 ( tran_id,date,type,emp,action,action_date) VALUES ( '314', 'Jun 29 2010 ', 'PRB', '881', 'CEMP', 'Jun 30 2010 ')
INSERT INTO @test-2 ( tran_id,date,type,emp,action,action_date) VALUES ( '314', 'Jun 29 2010 ', 'PYA', '881', 'INPH', 'Jul 1 2010 ')
INSERT INTO @test-2 ( tran_id,date,type,emp,action,action_date) VALUES ( '316', 'Jun 29 2010 ', 'PYN', '338', 'CEMP', 'Jul 1 2010 ')
INSERT INTO @test-2 ( tran_id,date,type,emp,action,action_date) VALUES ( '320', 'Jun 29 2010 ', 'MLA', '877', 'CEMP', 'Jul 1 2010 ')
INSERT INTO @test-2 ( tran_id,date,type,emp,action,action_date) VALUES ( '324', 'Jun 29 2010 ', 'PYA', '429', 'DHKP', 'Jul 1 2010 ')
INSERT INTO @test-2 ( tran_id,date,type,emp,action,action_date) VALUES ( '334', 'Jun 29 2010 ', 'PYA', '906', 'DHKP', 'Jul 1 2010 ')
INSERT INTO @test-2 ( tran_id,date,type,emp,action,action_date) VALUES ( '334', 'Jun 29 2010 ', 'CBK', '906', 'CEMP', 'Jul 1 2010 ')
INSERT INTO @test-2 ( tran_id,date,type,emp,action,action_date) VALUES ( '346', 'Jun 29 2010 ', 'SHD', '630', 'GECD', 'Jul 1 2010 ')
INSERT INTO @test-2 ( tran_id,date,type,emp,action,action_date) VALUES ( '360', 'Jun 29 2010 ', 'PYN', '669', 'CANC', 'Jul 1 2010 ')
INSERT INTO @test-2 ( tran_id,date,type,emp,action,action_date) VALUES ( '360', 'Jun 29 2010 ', 'RAE', '669', 'GMVD', 'Jul 1 2010 ')
INSERT INTO @test-2 ( tran_id,date,type,emp,action,action_date) VALUES ( '362', 'Jun 29 2010 ', 'PYA', '187', 'DHKP', 'Jul 1 2010 ')
INSERT INTO @test-2 ( tran_id,date,type,emp,action,action_date) VALUES ( '362', 'Jun 29 2010 ', 'PYA', '187', 'INPH', 'Jul 1 2010 ')
INSERT INTO @test-2 ( tran_id,date,type,emp,action,action_date) VALUES ( '362', 'Jun 29 2010 ', 'PYA', '187', 'INPH', 'Jul 1 2010 ')
INSERT INTO @test-2 ( tran_id,date,type,emp,action,action_date) VALUES ( '306', 'Jun 29 2010 ', 'PYN', '707', 'DHKP', 'Jul 1 2010 ')
INSERT INTO @test-2 ( tran_id,date,type,emp,action,action_date) VALUES ( '306', 'Jun 29 2010 ', 'PYN', '707', 'INPH', 'Jul 4 2010 ')
SELECT tran_id, emp, CANC, CEMP, DHKP, GECD, GMVD, IHHE, INPH
FROM
(SELECT tran_id, emp, action
FROM @test-2) AS SourceTable
PIVOT
(
count(action)
FOR action IN ([CANC], [CEMP], [DHKP], [GECD], [GMVD], [IHHE], [INPH])
) AS PivotTable;
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 1, 2010 at 12:24 pm
Hi
thanks for your reply but the actions are not specific or pre defined...I cannot hard code the actions as I dont know what action I may get....
Thanks [/font]
July 1, 2010 at 12:30 pm
how many actions can a tranid have? why do they have to separated into different columns? how will the table be queried (where action1 = 'ABCD' or action2 = 'ABCD' ....)?
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 1, 2010 at 12:37 pm
actually this table is not for actions it is for transaction, how many transactions came everyday, so for one transaction there may be 2-5 actions but I have to show them in the same transaction as it is suppose to be in same unless the tranid changes in the sequence, also same transaction can come two times in a day so i need two rows for that same transaction but everytime I will have to have some break in between those transaction ...as every hour i may get 100 transactions, so 100 * 4 actions per transaction = 400 rows...
this is the way i am loading data...one line for one transaction-showing all actions..
the tranid's are like account numbers....(one tranid remain constant)
hope you got it
Thanks [/font]
July 1, 2010 at 12:54 pm
First, there are a couple of issues with your structure. To put the transactions in order, there needs to be something to order it by. A datetime or identity counter or some other means must be used to order them. I've modified your table to do this, but you would need to add the mechanism to your real table if it does not already exist.
CREATE TABLE #test(
row_num INT IDENTITY,
tran_id nvarchar(14) NULL,
tran_type char(3) NULL,
emp char(5) NULL,
tran_action nvarchar(4) NULL
)
INSERT INTO #test (tran_id,tran_type,emp,tran_action) VALUES ('306', 'PRB', '229', 'IHHE')
INSERT INTO #test (tran_id,tran_type,emp,tran_action) VALUES ('306', 'PRB', '229', 'CEMP')
INSERT INTO #test (tran_id,tran_type,emp,tran_action) VALUES ('306', 'PYN', '885', 'DHKP')
INSERT INTO #test (tran_id,tran_type,emp,tran_action) VALUES ('306', 'PYN', '885', 'INPH')
INSERT INTO #test (tran_id,tran_type,emp,tran_action) VALUES ('314', 'PRB', '881', 'CEMP')
INSERT INTO #test (tran_id,tran_type,emp,tran_action) VALUES ('314', 'PYA', '881', 'INPH')
INSERT INTO #test (tran_id,tran_type,emp,tran_action) VALUES ('316', 'PYN', '338', 'CEMP')
INSERT INTO #test (tran_id,tran_type,emp,tran_action) VALUES ('320', 'MLA', '877', 'CEMP')
INSERT INTO #test (tran_id,tran_type,emp,tran_action) VALUES ('324', 'PYA', '429', 'DHKP')
INSERT INTO #test (tran_id,tran_type,emp,tran_action) VALUES ('334', 'PYA', '906', 'DHKP')
INSERT INTO #test (tran_id,tran_type,emp,tran_action) VALUES ('334', 'CBK', '906', 'CEMP')
INSERT INTO #test (tran_id,tran_type,emp,tran_action) VALUES ('346', 'SHD', '630', 'GECD')
INSERT INTO #test (tran_id,tran_type,emp,tran_action) VALUES ('360', 'PYN', '669', 'CANC')
INSERT INTO #test (tran_id,tran_type,emp,tran_action) VALUES ('360', 'RAE', '669', 'GMVD')
INSERT INTO #test (tran_id,tran_type,emp,tran_action) VALUES ('362', 'PYA', '187', 'DHKP')
INSERT INTO #test (tran_id,tran_type,emp,tran_action) VALUES ('362', 'PYA', '187', 'INPH')
INSERT INTO #test (tran_id,tran_type,emp,tran_action) VALUES ('362', 'PYA', '187', 'DHKP')
INSERT INTO #test (tran_id,tran_type,emp,tran_action) VALUES ('362', 'PYA', '187', 'INPH')
INSERT INTO #test (tran_id,tran_type,emp,tran_action) VALUES ('306', 'PYN', '707', 'DHKP')
INSERT INTO #test (tran_id,tran_type,emp,tran_action) VALUES ('306', 'PYN', '707', 'INPH')
Now, I believe this is what you're trying to get at, but this would just be the first step:
SELECT tran_id,
emp,
tran_action,
action_order = ROW_NUMBER() OVER (PARTITION BY tran_id ORDER BY row_num)
FROM #test
ORDER BY tran_id, action_order
To get your final result you would need to pivot/crosstab these results. If I understand you correctly, all you're wanting is the tran_id, the emp for the first action_order and then the list of actions in order of the action_order. The problem is that it sounds like the number of tran_actions is dynamic. So, if you want to break those out into separate columns you will need to use dynamic sql. Otherwise, if you want a list of them with a single column, you can do that using XML Path without having to resort to dynamic sql.
July 1, 2010 at 1:03 pm
Thanks for your reply,
Actually The result looks good but still there is something that I want to eliminate , i have to use the order coming from source, i cannot order it by tran id myself...
for example for tran id 306:
the result shows like this:
306229 IHHE1
306229 CEMP2
306885 DHKP3
306885 INPH4
306707 DHKP5
306707 INPH6
but it should be like this:
306229 IHHE1
306229 CEMP2
306229 DHKP3
306229 INPH4
306707 DHKP5
306707 INPH6
I dont want any row for emp = 885 ...as the sequence for 306 never broke in the source table between this:
1306PRB229 IHHE
2306PRB229 CEMP
3306PYN885 DHKP
4306PYN885 INPH
these four rows should give only one row
306229 IHHECEMP
i need to pick the first EMP only with all the actions there after, until the tran id changes.. that create second row for next tran id...
hope you got it
Thanks [/font]
July 1, 2010 at 2:07 pm
DO NOT use this code with reading Jeff Moden's article[/url] about the quirky update method.
You would still need to take this data and pivot/crosstab. If you want the sequence within one column I can show you how to do that but I'm not going to write the dynamic sql if you want them in separate columns ... you'll have to research that on your own.
CREATE TABLE #tempOrder
(
row_num INT PRIMARY KEY CLUSTERED,
tran_id NVARCHAR(14),
emp CHAR(5),
prior_emp CHAR(5),
tran_action NVARCHAR(4),
tranFlag BIT,
grpOrder INT
)
INSERT INTO #tempOrder (row_num, tran_id, emp, prior_emp, tran_action, tranFlag, grpOrder)
SELECT sq.row_num,
sq.tran_id,
sq.emp,
sq.prior_emp,
sq.tran_action,
sq.tranFlag,
grpOrder = CASE WHEN tranFlag = 1 THEN ROW_NUMBER() OVER(PARTITION BY tranFlag ORDER BY row_num) END
FROM
(
SELECT t.row_num,
t.tran_id,
t.emp,
prior_emp = tb.emp,
t.tran_action,
tranFlag = CASE WHEN ISNULL(tb.tran_id,0) <> t.tran_id THEN 1 END
FROM #test t
LEFT JOIN #test tb
ON tb.row_num = t.row_num - 1
) sq
ORDER BY sq.row_num
DECLARE @grpOrderSetting INT
SELECT @grpOrderSetting = 0
UPDATE #tempOrder
SET @grpOrderSetting = grpOrder =
CASE
WHEN tranFlag = 1 THEN grpOrder
WHEN emp = prior_emp THEN @grpOrderSetting END
FROM #tempOrder
OPTION (MAXDOP 1)
SELECT grpOrder,
tran_id,
emp,
tran_action,
action_order = ROW_NUMBER() OVER (PARTITION BY grpOrder ORDER BY row_num)
FROM #tempOrder
WHERE grpOrder IS NOT NULL
ORDER BY row_num
July 1, 2010 at 2:16 pm
OMG...thanks,its very complex code........ I know how to pivot /crosstab data, I can do that and post it here but first this is not happening at all...
the code is great but I think you didnt match the results with what I wanted, I want all the actions, I cannot miss actions, I ran your code for tranid =306 its missing two actions but.....
I think its gr8 code
SELECT grpOrder,
tran_id,
emp,
tran_action,
action_order = ROW_NUMBER() OVER (PARTITION BY grpOrder ORDER BY row_num)
FROM #tempOrder
WHERE grpOrder IS NOT NULL and tran_id =306
ORDER BY row_num
your result:
1306229 IHHE1
1306229 CEMP2
10306707 DHKP1
10306707 INPH2
--but it should be like this:
--306 229 IHHE 1
--306 229 CEMP 2
--306 229 DHKP 3
--306 229 INPH 4
--306 707 DHKP 5
--306 707 INPH 6
hope you got the missing portion...
Thanks [/font]
July 1, 2010 at 2:23 pm
Wow!!! bt....
this is an unbelievable code man.. I think I can use this part and find an answer let me give it a try..
thank you so much...I have been trying to get this whole day...
SELECT t.row_num,
t.tran_id,
t.emp,
prior_emp = tb.emp,
t.tran_action,
tranFlag = CASE WHEN ISNULL(tb.tran_id,0) <> t.tran_id THEN 1 END
FROM #test t
LEFT JOIN #test tb
ON tb.row_num = t.row_num - 1
Thanks [/font]
July 1, 2010 at 3:15 pm
Hopefully this is what you're wanting:
CREATE TABLE #tempOrder
(
row_num INT PRIMARY KEY CLUSTERED,
tran_id NVARCHAR(14),
emp CHAR(5),
prior_emp CHAR(5),
tran_action NVARCHAR(4),
tranFlag BIT,
grpOrder INT
)
INSERT INTO #tempOrder (row_num, tran_id, emp, prior_emp, tran_action, tranFlag, grpOrder)
SELECT sq.row_num,
sq.tran_id,
sq.emp,
sq.prior_emp,
sq.tran_action,
sq.tranFlag,
grpOrder = CASE WHEN tranFlag = 1 THEN ROW_NUMBER() OVER(PARTITION BY tranFlag ORDER BY row_num) END
FROM
(
SELECT t.row_num,
t.tran_id,
t.emp,
prior_emp = tb.emp,
t.tran_action,
tranFlag = CASE WHEN ISNULL(tb.tran_id,0) <> t.tran_id THEN 1 END
FROM #test t
LEFT JOIN #test tb
ON tb.row_num = t.row_num - 1
) sq
ORDER BY sq.row_num
DECLARE @grpOrderSetting INT,
@emp CHAR(5)
SELECT @grpOrderSetting = 0
SELECT @emp = ''
UPDATE #tempOrder
SET @grpOrderSetting = grpOrder =
CASE
WHEN tranFlag = 1 THEN grpOrder
ELSE @grpOrderSetting END,
@emp = emp =
CASE
WHEN tranFlag = 1 THEN emp
ELSE @emp END
FROM #tempOrder
OPTION (MAXDOP 1)
SELECT tran_id,
emp,
tran_action,
action_order = ROW_NUMBER() OVER (PARTITION BY tran_id ORDER BY row_num)
FROM #tempOrder
ORDER BY tran_id, row_num
July 2, 2010 at 7:03 am
Thanks a lot bt...
your code works like a charm...
this is exactly what I was looking for....
Thanks again...
Thanks [/font]
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply