Combining one column but keeping other column data

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

Combining one column but keeping other column data

Author

Topic

Tototo1
Starting Member

22 Posts

Posted - 2012-07-12 : 15:45:43

Hello,

I am trying to figure out a way to combine one column with another, but only when the column reflects the same number in a field tdindex. An example would probably be best here. Some of my data reads:

tindex tworkdt tworkdol tddesc tdline
1234 10/01/2010 1000.00 Hello my 1
1234 10/01/2010 1000.00 name is Al 2
5678 10/02/2010 500.00 Category B 1
6789 10/03/2010 4000.00 This is just 1
6789 10/03/2010 4000.00 great 2

I want to return all values (except tdline - this just represents how many lines are in the actual entry that is being broken up) with the tddesc for each tindex number combined, so for tdindex 1234 the tddesc should read 'Hello my name is Al' . My data restricts the amount of characters on line can have so it is breaking up each entry into multiple entries. There may be more than 2 lines to combine (in one case there is 9 lines) but I'd like to have just this field combined. After I run my SQL statement I'd like to have come out:

tindex tworkdt tworkdol tddesc
1234 10/01/2010 1000.00 Hello my name is Al
5678 10/02/2010 500.00 Category B
6789 10/03/2010 4000.00 This is just great

Thank you,
E

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-12 : 15:59:19

[code]SELECT
a.tindex,
a.tworkdt,
a.tworkdol,
STUFF(b.tddesc,1,1,'') AS CombinedDescr
FROM
YourTable a
CROSS APPLY
(
SELECT ','+b.tddesc AS [text()]
FROM YourTable b
WHERE b.tindex = a.tindex
ORDER BY b.tdline
FOR XML PATH('')
) b(tddesc)
GROUP BY
a.tindex,
a.tworkdt,
a.tworkdol,
b.tddesc[/code]

Tototo1
Starting Member

22 Posts

Posted - 2012-07-12 : 16:06:10

Sorry for my lack of knowledge but what is the b table all about? Not sure why you are using this, and how will be SQL know what table b is without actually having a table b?

xhostx
Constraint Violating Yak Guru

277 Posts

Posted - 2012-07-12 : 16:09:21

HE USES b AS AN ALIA (A NAME YOU CAN GIVE THE A TABLE OR A WHOLE SELECT)

AS YOU CAN SEE THE WHOLE SELECT IS BETWEEN PARENTHESIS, SO, HE CALLED IT "b"

--------------------------
Get rich or die trying
--------------------------

Tototo1
Starting Member

22 Posts

Posted - 2012-07-12 : 16:12:53

I am getting an Invlaid object name 'b'. error

xhostx
Constraint Violating Yak Guru

277 Posts

Posted - 2012-07-12 : 16:18:00

quote:
Originally posted by Tototo1

I am getting an Invlaid object name 'b'. error

If you are using SQL 2000, the CROSS APPLY will not execute because its not supported

--------------------------
Get rich or die trying
--------------------------

Tototo1
Starting Member

22 Posts

Posted - 2012-07-12 : 16:21:25

Gotcha, I think that is the problem. Doh!

Tototo1
Starting Member

22 Posts

Posted - 2012-07-12 : 16:29:37

Well is there any way to do this without using a CROSS APPLY? In my head it seems I might be able to use a fancy CASE statement maybe? Gonna give it a whirl but any other suggestions would be appreciated.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-12 : 21:17:02

quote:
Originally posted by Tototo1

Well is there any way to do this without using a CROSS APPLY? In my head it seems I might be able to use a fancy CASE statement maybe? Gonna give it a whirl but any other suggestions would be appreciated.

A fancy CASE statement is possible, but since you have up to 9 "tdlines" that need to be joined it would mean joining the table 9 times to itself which is unwieldy and probably inefficient.

I am not very familiar with SQL 2000, so the only thing that comes to mind is the "quirky update" approach. I am not a great fan of that method - it requires a clustered index and TABLOCKX and MAXDOP = 1 and all that stuff for it to work as expected. Even so, I am amazed (not in a good way) that it does seem to work always. You can read all the gory details at Jeff Moden's article here; be sure to read the RULES section in that article: http://www.sqlservercentral.com/articles/T-SQL/68467/

In any case, this is an example using quirky update method:

-- TEST TABLE
CREATE TABLE tmp(tindex INT, tworkdt date, tworkdol FLOAT, tddesc VARCHAR(255), tdline INT);

-- INSERT DATA IN RANDOM ORDER
INSERT INTO tmp VALUES (6789,'10/03/2010', 4000.00,'This is just',1 )
INSERT INTO tmp VALUES (1234,'10/01/2010', 1000.00,'name is Al',2 )
INSERT INTO tmp VALUES (1234,'10/01/2010',1000.00,'Hello my',1)
INSERT INTO tmp VALUES (5678,'10/02/2010', 500.00,'Category B',1)
INSERT INTO tmp VALUES (6789,'10/03/2010',4000.00,'great',2 );


-- CREATE A CLUSTERED INDEX. WITHOUT THIS, THE METHOD WON'T WORK CORRECTLY.
-- IT AMAZES ME THAT IT APPEARS TO WORK WHEN YOU DO HAVE THE CLUSTERED INDEX
CREATE UNIQUE CLUSTERED INDEX IDX_TIndex_TDLine ON [dbo].[tmp] 
(
	[tindex] ASC,
	[tdline] ASC
)

-- OH, DID I MENTION WE NEED A PLACEHOLDER COLUMN?
ALTER TABLE tmp ADD PlaceHolder VARCHAR(8000)

DECLARE @placeHolder VARCHAR(8000), @tindex INT;
SET @placeHolder = '';

-- QUIRKY UPDATE WITH ALL ITS QUIRKS AND MYSTERIES
UPDATE  tmp SET
	PlaceHolder = @placeHolder,
	@placeHolder = 
		CASE 
			WHEN @tindex = tindex THEN @placeHolder + ' ' +  tddesc
			ELSE tddesc
		END,
	@tindex = tindex 
FROM tmp 
	WITH (TABLOCKX)  -- eeew!!
	OPTION (MAXDOP 1) -- more eeeeeeew!!

-- TADA! IT DOES WORK.	
SELECT 
	tindex,tworkdt,tworkdol, MAX(PlaceHolder) AS tddesc 
FROM
	tmp
GROUP BY
	tindex,tworkdt,tworkdol;

-- OK, DROP THE TABLE. BUT WHEN YOU WORK ON YOUR ACTUAL TABLE DONT DROP IT!! 
DROP TABLE tmp;

xhostx
Constraint Violating Yak Guru

277 Posts

Posted - 2012-07-13 : 08:53:57

@sunitabeck, Great Job :).

--------------------------
Get rich or die trying
--------------------------

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-13 : 09:15:40

Thank you xhostx!

(assuming you were serious and not smiling at my attempt to use undocumented features which would work most of the time and will fail only at the most critical moment when you really really needed it to work )

Tototo1
Starting Member

22 Posts

Posted - 2012-07-13 : 11:01:06

sunitabeck ... brav, frickin - O! Works beautifully. Needed to tweak a query I made earlier so remove a couple duplicates that were causing this to fail but once I figured that out, it worked! I am going to study this a bit to see if I can wrap my head around what is actually happening in this query, but honestly, I can't thank you enough!

Tototo1
Starting Member

22 Posts

Posted - 2012-07-13 : 11:04:59

If you have any time at all to help a newbie out, can you explain what the @ are used for? Not really understanding the following statement, and how it is actually working.

WHEN @tindex = tindex THEN @placeHolder + ' ' + tddesc

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-13 : 13:16:48

Tokens that you see with a prefixed @ sign are VARIABLEs. http://msdn.microsoft.com/en-us/library/ms188927.aspx

DECLARE @placeHolder VARCHAR(8000), @tindex INT;

This declared two variables, one of type varchar(8000) and the other of type integer.

WHEN @tindex = tindex THEN @placeHolder + ' ' + tddesc

This is what the quirky update is about. It is updating a table and a variable at the same time - which is not something that you normally do. Read Jeff Moden's article that I mentioned in my previous post - that explains the concept with examples.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-07-13 : 13:22:28

Just be careful if this is production code as the Quirky update is not guaranteed to work in any version of SQL server (it just happens to right now, and probably will continue to work).

xhostx
Constraint Violating Yak Guru

277 Posts

Posted - 2012-07-13 : 14:28:10

quote:
Originally posted by sunitabeck

Thank you xhostx!

(assuming you were serious and not smiling at my attempt to use undocumented features which would work most of the time and will fail only at the most critical moment when you really really needed it to work )

No seriously, I said it and proud of you ;)

--------------------------
Get rich or die trying
--------------------------

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-13 : 14:48:36

aw.. thank you!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources