March 29, 2013 at 9:25 am
The type and task column are combined into one table task keeping in mind the order they have to come in.
current table
Procseqtypetask
11a1this is
12b2supposed
21a1to
22b2be a
23c3test db
24c3thank you
desired table
Procseqtask
11a1
11this is
12b2
12supposed
21a1
21to
22b2
22be a
23c3
23test db
23thank you
March 29, 2013 at 9:31 am
no rules for what it is you want to do?
my wild guess is you want to split Task by word, but no explanation ont he numbering scheme.
With MySampleData ([Proc],Seq,Type,Task)
AS
(
SELECT '1','1','a1','this is ' UNION ALL
SELECT '1','2','b2','supposed ' UNION ALL
SELECT '2','1','a1','to ' UNION ALL
SELECT '2','2','b2','be a ' UNION ALL
SELECT '2','3','c3','test db' UNION ALL
SELECT '2','4','c3','thank you'
)
SELECT *
from MySampleData
cross apply dbo.DelimitedSplit8K(Task,' ') myfn
DelimitedSplit8K Explanation[/url]
Lowell
March 29, 2013 at 10:11 am
Additional info from a duplicate post:
knakka 14475 (3/29/2013)
This current table has to be converted to the @temp_outOrder would be like this Proc --> Type --> Individual task for that type
the order of the @temp_out task col is defined like Order by type is base on the seq col then the each Type will have the list of task below it
/*Current Table*/
Declare @temp Table ([Proc] INT,Seq INT,Type VARCHAR(2),Task VARCHAR(20))
INSERT INTO @TEMP
SELECT '1','1','a1','this is ' UNION ALL
SELECT '1','2','b2','supposed ' UNION ALL
SELECT '2','1','a1','to ' UNION ALL
SELECT '2','2','b2','be a ' UNION ALL
SELECT '2','3','c3','test db' UNION ALL
SELECT '2','4','c3','thank you'
SELECT * FROM @TEMP
/*Desired Table */
Declare @temp_out Table ([Proc] INT,Seq INT,Task VARCHAR(20))
INSERT INTO @temp_out
select '1','1','a1' union
select '1','1','this is ' union
select '1','2','b2' union
select '1','2','supposed ' union
select '2','1','a1' union
select '2','1','to ' union
select '2','2','b2' union
select '2','2','be a ' union
select '2','3','c3' union
select '2','3','test db' union
select '2','3','thank you'
SELECT * FROM @temp_out
March 29, 2013 at 10:25 am
This works for your sample data. I changed the column names because dealing with reserved words as column names drives me nutty.
Declare @temp Table
(
MyProc INT,
MySeq INT,
MyType VARCHAR(2),
MyTask VARCHAR(20)
)
INSERT INTO @TEMP
SELECT '1','1','a1','this is ' UNION ALL
SELECT '1','2','b2','supposed ' UNION ALL
SELECT '2','1','a1','to ' UNION ALL
SELECT '2','2','b2','be a ' UNION ALL
SELECT '2','3','c3','test db' UNION ALL
SELECT '2','4','c3','thank you'
;with MyCte as
(
select MyProc, MySeq, MyType, 1 as SortOrder from @temp
union all
select MyProc, MySeq, MyTask, 2 from @temp
)
select MyProc, MySeq, MyType
from MyCte
order by MyProc, MySeq, SortOrder
_______________________________________________________________
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/
March 29, 2013 at 10:28 am
Actually my code doesn't work because you don't have anything you can use to sort with. There appears to be nothing you can use as a primary key or even a decent sort order.
_______________________________________________________________
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/
March 29, 2013 at 10:31 am
If I add an identity to your table variable this works.
Declare @temp Table
(
MyKey int identity,
MyProc INT,
MySeq INT,
MyType VARCHAR(2),
MyTask VARCHAR(20)
)
INSERT INTO @TEMP
SELECT '1','1','a1','this is ' UNION ALL
SELECT '1','2','b2','supposed ' UNION ALL
SELECT '2','1','a1','to ' UNION ALL
SELECT '2','2','b2','be a ' UNION ALL
SELECT '2','3','c3','test db' UNION ALL
SELECT '2','4','c3','thank you'
;with MyCte as
(
select MyKey, MyProc, MySeq, MyType, 1 as SortOrder from @temp
union all
select MyKey, MyProc, MySeq, MyTask, 2 from @temp
)
select MyProc, MySeq, MyType
from MyCte
order by MyKey, SortOrder
_______________________________________________________________
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/
April 1, 2013 at 1:31 pm
Hi Sean thank you for the reply. I really appreciate the help.
I have one issue with the result.. Mytype C3 has to have its results group into one.. like
MyProcMySeqMyType
11a1
11this is
12b2
12supposed
21a1
21to
22b2
22be a
23c3
23test db
24c3
24thank you
MyProcMySeqMyType
11a1
11this is
12b2
12supposed
21a1
21to
22b2
22be a
23c3
23test db
24thank you
April 1, 2013 at 1:55 pm
Something like this would work?
(Using the sample data provided before)
SELECT * FROM (
SELECT [Proc], MIN( Seq) Seq, Type AS Task
FROM @temp
GROUP BY [Proc], Type) A
UNION
SELECT [Proc], Seq, Task
FROM @temp
April 1, 2013 at 2:25 pm
cool it worked like a charm..
April 1, 2013 at 2:48 pm
You can use it as a CTE as well, instead of the subquery.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy