Using a wild card in place of field name

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

Using a wild card in place of field name

Author

Topic

dzabor
Posting Yak Master

138 Posts

Posted - 2012-08-10 : 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

52326 Posts

Posted - 2012-08-10 : 23:48:07

so what should be output?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dzabor
Posting Yak Master

138 Posts

Posted - 2012-08-10 : 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

52326 Posts

Posted - 2012-08-10 : 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

138 Posts

Posted - 2012-08-11 : 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

52326 Posts

Posted - 2012-08-11 : 00:17:21

sounds like unpivot to me

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dzabor
Posting Yak Master

138 Posts

Posted - 2012-08-14 : 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

52326 Posts

Posted - 2012-08-14 : 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

138 Posts

Posted - 2012-08-14 : 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

52326 Posts

Posted - 2012-08-14 : 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/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources