| Author |
 Topic  |
|
|
dzabor
Posting Yak Master
USA
126 Posts |
 Posted - 08/10/2012 : 22:56:41
|
Using the two tables below, I need to link the Information_Schema.column to any demographics field without specifically naming them. Are there wild card like Demographics.% that I can use to link the two together?
The bigger picture is I am trying to link them to another table with the codes and descriptions to be able to update the codes for each ID.
Information_schema
Table_Name Column_Name
Demographics military_codes
Demographics TEST_SCORES
Demographics table
ID Birthdate military_codes TEST_SCORES
101 10/15/1987 03, 04, 05 A, b A C
Thanks!
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47145 Posts |
Posted - 08/10/2012 : 23:48:07
|
so what should be output?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
 |
|
|
dzabor
Posting Yak Master
USA
126 Posts |
Posted - 08/10/2012 : 23:54:50
|
| i will be using another table that will update the codes to descriptions. I need to be able to link any of these tables to the field name. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47145 Posts |
Posted - 08/10/2012 : 23:58:21
|
sorry i didnt get that...whats the relevance of information_schema table in this?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
dzabor
Posting Yak Master
USA
126 Posts |
Posted - 08/11/2012 : 00:08:27
|
I do not have to use that table, but as an example all of the tables I need layout the 'military_codes' with all of the information ready to use, however there is no way to link back to the actual data field that has the record information in it.
I need to be able to get the demographic's table to be able to link up any of the column names back to another table without specifying the exact name every time. There are several table that will show all of the fields such as the schema. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47145 Posts |
Posted - 08/11/2012 : 00:17:21
|
sounds like unpivot to me
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
dzabor
Posting Yak Master
USA
126 Posts |
Posted - 08/14/2012 : 13:22:06
|
I have been reviewing your suggestion, but that seems tio be more for aggregates.
I cam see how it reverses columns with rows, but cannot find any good examples.
See below:
My data -
ID First Last SpecialField
101 Joe Smith Golf
102 Suzie Snowflake Tennis
Data return needed -
ID 101 102
First Joe Suzie
Last Smith Snowflake
Special Field Golf Tennis
Thanks! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47145 Posts |
Posted - 08/14/2012 : 13:36:55
|
you cant confirm unless you try it.
see illustration below
declare @t table
(
ID int,
[First] varchar(100),
[Last] varchar(100),
SpecialField varchar(100)
)
insert @t
values(101,'Joe','Smith','Golf'),
(102, 'Suzie' ,'Snowflake', 'Tennis')
select *
from @t
unpivot(columnvalue for columnname IN ([First],[Last],[SpecialField]) )u
pivot(max(columnvalue) for ID IN ([101],[102]))p
columnname 101 102
--------------------------------
First Joe Suzie
Last Smith Snowflake
SpecialField Golf Tennis
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
dzabor
Posting Yak Master
USA
126 Posts |
Posted - 08/14/2012 : 14:14:47
|
It comes back with errors:
Msg 170, Level 15, State 1, Line 9
Line 9: Incorrect syntax near ','.
Msg 170, Level 15, State 1, Line 14
Line 14: Incorrect syntax near 'columnvalue'
Also this may be fine for when inserting the datavalues, but I want to run it against a table without inserting all of the values. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47145 Posts |
Posted - 08/14/2012 : 14:53:06
|
that insert was just for illustration. you can run select part alone for your table.
the insert code part will error if you try running on version before 2008
select *
from @t
unpivot(columnvalue for columnname IN ([First],[Last],[SpecialField]) )u
pivot(max(columnvalue) for ID IN ([101],[102]))p
put your table name and column names in place of @t and columns
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
Using a wild card in place of field name
