Dynamic Per Row Column Names - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

Dynamic Per Row Column Names

Author

Topic

Yisman
Starting Member

8 Posts

Posted - 2008-10-23 : 13:34:05

Hi Gurus!

My database has addresses from several different countries. Different countries have different formats of how they set up address labels. Some countries you have:
123 main street
MyCity, MyState 98765

in other places addresses are written out as:
Main Street 123
98765 MyCity

and many other variations (apt/flat/floor/po box...)

Hence, I have a table Countries which has a field AddressFormat where i put a sql clause for each country. here are 2 samples:

CountryName / AddressFormat

Austria / Street + IsNull(HouseNum,'') + CHAR(10) + ZipCode
USA / IsNull(HouseNum,'') + Street + CHAR(10) + CityName

My question is, is there any way to return a view or sproc which shows a formatted address for each row according to the field list in "AddressFormat" of particular country?

As far as I saw, Dynamic SQL via EXEC cannot change the columns on a per-row basis.

I know I could work around with clientside String.Replace functions, but I am looking for a more elegent solution.

I hope my question is clear, otherwise I'd be glad to rephrase.

Thank you all for your time and patience

Yisman

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-23 : 14:29:25

Let me make this clear:

You have a table of addresses:


HOUSENUM   STREET    CITYNAME    STATE   ZIP
---------  -------   ---------   ------  ------
123        MAIN ST   New York    NY      10000

You have another table of countries:


Country Name          Address Format
-----------------     ----------------------------------------------------
USA                   Street + IsNull(HouseNum,'') + CHAR(10) + ZipCode
AUSTRIA               IsNull(HouseNum,'') + Street + CHAR(10) + CityName

How you want to show this Address in this format:


US       123 MAIN ST
         10000

AUSTRIA  MAIN ST 123
         New York

Assume AddressFormat column contains the SQL to display for each country...

Yisman
Starting Member

8 Posts

Posted - 2008-10-23 : 17:28:36

Hi Hanbingl!
Thank you for joining!

You are 100% correct. I couldn't (and have'nt ;-)) have explained it any better then that.
just for completeness ill add that i have another 2 tables that links every city to a state (-"Cities") and one that links every state to a country(-"States"). but it is really beside the point.
awaiting your suggestion!
Yisman

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-23 : 18:47:54

[code]
declare @sql varchar(8000)

select @sql='
create table #tmpadr(ID int,
HOUSENUM varchar(10),
STREET varchar(20),
CITYNAME varchar(20),
STATE varchar(2),
ZIPCODE varchar(10),
Country varchar(10),
Format varchar(8000),
NEWADDR VARCHAR(8000)
)
insert into #tmpadr (ID, HOUSENUM, STREET, CITYNAME, STATE, ZIPCODE, Country, Format)
select a.ID, HOUSENUM, STREET, CITYNAME, STATE, ZIPCODE, b.countryname, b.addressfmt from address a, country b;
declare @sql varchar(8000),@format varchar(8000)
set nocount on
declare cur cursor for
select distinct Format from #tmpadr
OPEN cur
FETCH NEXT FROM cur INTO @format
WHILE @@FETCH_STATUS=0
BEGIN
set @sql=''update #tmpadr set newaddr = ''+@format+'' where Format=''''''+replace(@format,'''''''','''''''''''')+''''''''
execute(@sql)
FETCH NEXT FROM cur INTO @format
END
CLOSE cur
DEALLOCATE cur
select country, NEWADDR from #tmpadr
drop table #tmpadr
'
set nocount on
execute(@sql)[/code]

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-23 : 18:55:34

Trust me, you want to leave the formatting to the Front end

Yisman
Starting Member

8 Posts

Posted - 2008-10-24 : 02:53:34

Hi Hanbingl!
Thanks for your posts.
If the only way to to it in SQL is via a cursor loop then, yes, I will be leaving it to the presentation logic.
Thanks for clearing it up for me.

If anyone knows of a simpler way to accomplish this in MSSQL, I'd very appreciate their ideas.

Thank you all

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-24 : 03:56:36

quote:
Originally posted by Yisman

Hi Hanbingl!
Thanks for your posts.
If the only way to to it in SQL is via a cursor loop then, yes, I will be leaving it to the presentation logic.
Thanks for clearing it up for me.

If anyone knows of a simpler way to accomplish this in MSSQL, I'd very appreciate their ideas.

Thank you all

It can't be done with cursor or while loop

Madhivanan

Failing to plan is Planning to fail

Yisman
Starting Member

8 Posts

Posted - 2008-10-24 : 05:18:27

hi Madhivanan
thanks for joining
can't or can?
that it can be done with iteration, we see from Hanbingl's post. do you have a different, non-looping, way?
Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-24 : 05:40:44

quote:
Originally posted by Yisman

hi Madhivanan
thanks for joining
can't or can?
that it can be done with iteration, we see from Hanbingl's post. do you have a different, non-looping, way?
Thanks

It can be done only with loop

Madhivanan

Failing to plan is Planning to fail

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-10-24 : 06:17:47

quote:

It can be done only with loop

Madhivanan

Failing to plan is Planning to fail

It can. But please, for the love of Codd, don't. Do it in your application instead after querying the Locale.

-------------
Charlie

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-24 : 06:42:48

quote:
Originally posted by Transact Charlie

quote:

It can be done only with loop

Madhivanan

Failing to plan is Planning to fail

It can. But please, for the love of Codd, don't. Do it in your application instead after querying the Locale.

-------------
Charlie

I always suggest that

I also suggest to do these in front end application

1 Date formation
2 Generate Serial No
3 Generate Running Total
4 Suppress Duplicates
5 Currency formation
6 Concatenate values

If you joined the forums before 2007, you might have seen all my "Do it in front end" replies

Madhivanan

Failing to plan is Planning to fail

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-10-24 : 06:49:18

quote:

I always suggest that
I also suggest to do these in front end application

1 Date formation
2 Generate Serial No
3 Generate Running Total
4 Suppress Duplicates
5 Currency formation
6 Concatenate values

If you joined the forums before 2007, you might have seen all my "Do it in front end" replies

Madhivanan

I think I joined midway through 2007. Can't remember. The shortest and most frequent comments I remember are the ones that said:

"How to ask a question": and then a link to a very concise page.

I agree with all your points except maybe number 4) Suppress Duplicates. Surely better to modify query so that it doesn't spit out dups in the first place. (NB: I don't mean just add SELECT DISTINCT!)

-------------
Charlie

Yisman
Starting Member

8 Posts

Posted - 2008-10-24 : 07:15:36

not all your "rules" are clear, id be happy for more insight
1&5 are obvoius. formatting is always frontend
2: do u mean item number for each product? i ALWAYS use an identity column for that, with gr8 success, i also leave an optional field 4 users 2 fill in with their own homegrown ids or SNs
3: totals are split. some totals are simple SUMs. but there are totals that factor many parameters, and for consistency, its best put into the view. i.e. qty*unitprice-discount-CustDiscountPercent*tax etc. keeping it in the BE makes sure u never skip a field or miscalc
4: u mean unique db values. thats what indeciis r 4, no? front end cannot be counted on
6: as with totals, 4 consistency i usually do it in a view. imagine rewriting alll over an expression like "isnull(prefix+' ','')+ isnull(firstname,'')+ isnull(lastname,'')" NEVER!
that is THE reason i wanted 2 do the address formatting in the BE, Consistency. But not for any price...

all the best

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-10-24 : 08:00:46

Hi Yisman,

I think Madhi's points are easily broken down into:

1) Don't mess with the data-types
2) Don't ask the server to do things it's not good/efficient at

So for his points.....

1 Date formation (don't screw with the data. it's passed to your application in a nice consistent type (DATETIME) and then you can format it there)

2 Generate Serial No (Actually I'm not sure what he means by this. Because he says Generate I'm assuming that no unique Serial number exists for whatever he brings back)

3 Generate Running Total (best way to do this is with a nice little loop. Therefore don't do it in db. do it in your iterative app instead)

4 Suppress Duplicates (You are artificially hiding the data from the application here. If your data actually contains dups (rather than arising from misbehaving query) then you should report them).

5 Currency formation (See point 1)

6 Concatenate values (See point 3)

Madhivanan, I'd be interested if you could explain what you mean in point 2. I think I've got a handle on your other points.

-------------
Charlie

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-24 : 08:16:18

<<
Madhivanan, I'd be interested if you could explain what you mean in point 2. I think I've got a handle on your other points.
>>

Assume that your procedure returns N number of rows based on the values of input parameters
What, if you want to show serial number as the first column?
Though can be done using row_number() in SQL Server 2005 onwards, it is ineffecient in earlier versions

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-24 : 08:22:02

Also note that by Suppress Duplicates I actually meant


Sample data

col1	col2
-------------
test1	100
test1	9822
test1	81123
test2	100
test2	1330
test3	123
test3	56

Expected result

col1	col2
-------------
test1	100
	9822
	81123
test2	100
	1330
test3	123
	56

Madhivanan

Failing to plan is Planning to fail

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-10-24 : 08:23:28

Ah I see. Thanks for explaining.

I love ROW_NUMBER(). So useful for so many situations.

NB: - And I see exactly what you mean about the dups now also.

-------------
Charlie

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources