club to columns into one

Question

Post reply

club to columns into one

knakka99

Say Hey Kid

Points: 671
More actions
March 29, 2013 at 9:25 am

#272284

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

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Additional info from a duplicate post:

knakka 14475 (3/29/2013)
This current table has to be converted to the @temp_out
Order 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

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

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/

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

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/

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

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/

knakka99 Say Hey Kid Points: 671 More actions · Answer 6

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 7

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

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

knakka99 Say Hey Kid Points: 671 More actions · Answer 8

knakka99

Say Hey Kid

Points: 671

April 1, 2013 at 2:25 pm

#1602592

cool it worked like a charm.. 🙂

Luis Cazares SSC Guru Points: 183706 More actions · Answer 9

You can use it as a CTE as well, instead of the subquery.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2