dodgy table design - 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 2000 Forums

Transact-SQL (2000)

dodgy table design

Author

Topic

jamie
Aged Yak Warrior

542 Posts

Posted - 2003-12-09 : 09:14:40

hello,
I am trying to create a report from a database and the data is held in tables like :

Data Table
ID
FieldID
Data

Field Table
ID
FieldID
Reference

where Reference = Forename, surname, dob, (the field names for the data held in Data_Table.Data

how can I create a query to show the data in a 'standard' design such as :

Desired Table
ID
Forename
Surname
DOB
etc.

hope this makes sense to you guys.

so far the only way I can think is to create individual statements doing like :
select id, data as forename from data_table d, field_table f where
d.fieldID=f.Id AND Reference = 'Forename'

thanks for any help.

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2003-12-09 : 09:28:04

This is a cross-tab solution

SELECT	f.ID
	, MAX(CASE WHEN f.Reference = 'Forename' THEN d.data ELSE NULL END) 'Forename'
	, MAX(CASE WHEN f.Reference = 'Surname' THEN d.data ELSE NULL END) 'Surname'
	....
FROM	data_table d JOIN field_table f
		ON d.fieldID = f.ID
GROUP BY ID

If you cannot definitively set the number of column attributes you should look at Robvolk's dynamic crosstab article:
[url]http://www.sqlteam.com/item.asp?ItemID=2955[/url]

jamie
Aged Yak Warrior

542 Posts

Posted - 2003-12-09 : 10:15:25

genius !#

cheers mate, thats helped me so much !

jamie
Aged Yak Warrior

542 Posts

Posted - 2003-12-09 : 10:17:20

sorry drymchaser, one quick question one if my fields is suppose to be an integer, but it has a data type of varchar.
when trying to sort by this field its not sorting correctly,
any way I can convert the varchar to an intso I can the order by that field ?

thank you.

jamie
Aged Yak Warrior

542 Posts

Posted - 2003-12-09 : 11:04:53

subject : using where clauses with cross tabs.

I am trying to add a where clause but keep getting error = invalid column name surname.
eg, where surname = 'jones'

any suggestions ?

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2003-12-09 : 11:18:02

[code]SELECT f.ID
, MAX(CASE WHEN f.Reference = 'Forename' THEN d.data ELSE NULL END) 'Forename'
, MAX(CASE WHEN f.Reference = 'Surname' THEN d.data ELSE NULL END) 'Surname'
....
FROM data_table d JOIN field_table f
ON d.fieldID = f.ID
GROUP BY ID
HAVING MAX(CASE WHEN f.Reference = 'Surname' THEN d.data ELSE NULL END) = 'Jones'[/code]

Look up CAST() or CONVERT() in BOL and put in ORDER BY clause.

jamie
Aged Yak Warrior

542 Posts

Posted - 2003-12-09 : 11:25:58

lovely stuff !
thanks, I'll look into Cast and convert to change from varchar to int.

cheers.
Jamie

X002548
Not Just a Number

15586 Posts

Posted - 2003-12-09 : 11:32:20

You would want to look at CONVERT(int, col1)

But if the column has any non numeric data you're hosed..

You can see if it has any nonnumeric data with

SELECT * FROM table WHERE ISNUMERIC(Col1) = 0

Brett

8-)

jamie
Aged Yak Warrior

542 Posts

Posted - 2003-12-09 : 11:44:44

thanks for the info Brett.
I'v managed to convert a varchar to an int, luckily all data is numeric !
however, when I do convert(datetime, thedate) I get the error :

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

this might be because one server is american and one is UK ?
any ideas ?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-12-09 : 11:59:35

this might be one of the worst table designs i have ever seen! yikes!

FYI -- if you use dervied tables, you can filter, sort and reference
columns by their new names after you cross tab:

SELECT *
FROM
(
SELECT f.ID
, MAX(CASE WHEN f.Reference = 'Forename' THEN d.data ELSE NULL END) 'Forename'
, MAX(CASE WHEN f.Reference = 'Surname' THEN d.data ELSE NULL END) 'Surname'
....
FROM data_table d JOIN field_table f
ON d.fieldID = f.ID
GROUP BY ID
) A
WHERE Surname = 'Jones'

I would recommend creating views for all of these tables if possible, so in the future you can just references the views. Have each view do all the crosstabing and assign field names. As new "columns" are created, you would then need to alter the views.

At least then you can kind of work with it as if it were a normal database .... but you will probably have performance issues since you will probably never be able to make use of any indexes.

- Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2003-12-09 : 14:57:33

quote:
Originally posted by jamie

thanks for the info Brett.
I'v managed to convert a varchar to an int, luckily all data is numeric !
however, when I do convert(datetime, thedate) I get the error :

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

this might be because one server is american and one is UK ?
any ideas ?

Check out IsDate next to find the offending date rows...

Brett

8-)

jamie
Aged Yak Warrior

542 Posts

Posted - 2003-12-11 : 06:18:17

hi guys, thanks for all your help so far.
I have used ISDATE() to find incorrect dates and my problem is I want to copy the data from one server UK to another US.
what CONVERT or CAST do I need to use to convert a UK date to US format ?

thanks again,
Jamie

jamie
Aged Yak Warrior

542 Posts

Posted - 2003-12-11 : 07:18:48

nevermind, I think I've sorted it :
I'm doing :

SUBSTRING(d.data,4,2) + ''/'' + LEFT(d.data,2) + ''/'' + RIGHT(d.data,4)

spireite
Starting Member

12 Posts

Posted - 2003-12-15 : 08:34:17

I'd use

CONVERT(VARCHAR(8), THEDATE, 112)

The date is returned in a ISO format, making it fully international.

mr_mist
Grunnio

1870 Posts

Posted - 2003-12-15 : 09:02:09

quote:
Originally posted by jamie

nevermind, I think I've sorted it :
I'm doing :

SUBSTRING(d.data,4,2) + ''/'' + LEFT(d.data,2) + ''/'' + RIGHT(d.data,4)

You should do this conversion as

INSERT...
SELECT convert(datetime, d.data, 103)

Assuming that you are INSERTing it with the code running on the .US server. The convert will then be expecting an input value in UK format, and should return a datetime in internation format (or server default, I'm not sure).

Do not use the substring stuff. It relies too much on various unknowns, eg, 1 character date values can mess it up easily.

-------
Moo. :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources