need sql query to convert a column of a table into meaningful data

Question

need sql query to convert a column of a table into meaningful data

Viewing 15 posts - 31 through 44 (of 44 total)

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

mario.balatellii (12/7/2011)
Hi Chris ..is it possible to get the output in a different way.
I want the heading to be like
TYPE BANKNAME MERCHANTCODE XYZ MscTimeStamp
8 HSBC NEXT 123 2007-09-18 15:57:18.340
8 BARC DEB 123 2007-09-17 12:51:18.240
This mean instead of getting 10 rows for 1 description column. I can somehow get only one row with the heading. I hope i am making sence :S

Yes it makes sense, It's a PIVOT or cross tab of your data.

Where do you get your column headings from? In the code I posted, I've hard-coded as follows:

SELECT [Entity code] = '9', [Entity name] = 'Bank name' UNION ALL

SELECT '35', 'Type' UNION ALL

SELECT '49', 'Merchant code'

Do you have a lookup table or will you have to hard-code?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

freecoder SSC Eights! Points: 951 More actions · Answer 2

freecoder

SSC Eights!

Points: 951

December 7, 2011 at 9:26 am

#1418210

ok

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

mario.balatellii (12/7/2011)
I will be hard coding the headings and I want the respective data for the row under that heading.

No problem. Don't forget to shape your sample data so that the output makes sense.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

freecoder SSC Eights! Points: 951 More actions · Answer 4

freecoder

SSC Eights!

Points: 951

December 7, 2011 at 9:46 am

#1418228

thx

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Crosstab version:

SELECT

[Type]= MAX(CASE WHEN en.[Entity name] = 'Type' THEN Attribute END),

[Bank name]= MAX(CASE WHEN en.[Entity name] = 'Bank name' THEN Attribute END),

[Merchant code] = MAX(CASE WHEN en.[Entity name] = 'Merchant code' THEN Attribute END),

[XYZ]= MAX(CASE WHEN en.[Entity name] = 'XYZ' THEN Attribute END),

MsgTimeStamp

FROM #SampleTable s

CROSS APPLY dbo.StringSplitter(s.[Description],'') split

CROSS APPLY (

SELECT

[Entity code] = LEFT(split.Item,d.pos-1),

Attribute = SUBSTRING(split.Item,d.pos+1,LEN(split.Item)-d.pos),

Pos

FROM ( SELECT Pos = CHARINDEX('=',split.Item) ) d

WHERE d.pos < DATALENGTH(split.Item)) x

LEFT JOIN (

SELECT [Entity code] = '9', [Entity name] = 'Bank name' UNION ALL

SELECT '35', 'Type' UNION ALL

SELECT '49', 'Merchant code'

) en ON en.[Entity code] = x.[Entity code]

GROUP BY MsgTimeStamp

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

freecoder SSC Eights! Points: 951 More actions · Answer 6

thanks for this Chris. I will try this at work tomoro and let you know how it goes. Much appreciated

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

mario.balatellii (12/7/2011)
thanks for this Chris. I will try this at work tomoro and let you know how it goes. Much appreciated

You're welcome Mario. You will need to change the name of the splitter to match yours, otherwise I think it's good to go.

You've been great to work with - thanks!

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

freecoder SSC Eights! Points: 951 More actions · Answer 8

freecoder

SSC Eights!

Points: 951

December 8, 2011 at 5:14 am

#1418608

ok

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

Probably easiest if you run the results into a local temp table...

SELECT ... INTO #Temp FROM ...

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

freecoder SSC Eights! Points: 951 More actions · Answer 10

yeah thats what i was thinking as well.. Thanks.

Or may be i will have sql job which will transfer the data into a table in a database. What do u think is much better

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 11

mario.balatellii (12/8/2011)
yeah thats what i was thinking as well.. Thanks.
Or may be i will have sql job which will transfer the data into a table in a database. What do u think is much better

If it's transient data - which it is, because as soon as you pull it, it's out of date - then I'd put it into a transient table.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

freecoder SSC Eights! Points: 951 More actions · Answer 12

freecoder

SSC Eights!

Points: 951

December 8, 2011 at 6:25 am

#1418642

cool. Thanks mate.

Ellen-477471 SSCrazy Points: 2238 More actions · Answer 13

Is this something that you need to do once or regularly? I don't know who designed the table and specifically the description column, but it is truly a bad design.

If you have control over changing the design of the table , so that you could split this column into multiple columns, I could recommend a way to split it apart to reload into the redesigned table.

Even with 500,000 records you could select the records and paste into Excel using text to column to divide the values.

This assumes that each 'description' contains the same number of values, in the same order.

Just a thought.

Good luck