Transposing data into fields in a query

[fbarbeite]

Hello everyone,

First off, I am not a programmer or database manager or administrator, so I have only an introductory knowledge of SQL. I am using Crystal Reports to report data stored in a SQL Server database. I know I must create a virtual table, which I can do in Crystal Reports, and I can use most of the info available on the web on SQL to create most of the syntax I need. However, there is one thing I don’t know how to do.

There are several fields I use to identify individuals and groups of data within the database. For example, email, work location, and business unit. Within the database, there is a table with one field called UserFieldName. The data in this field are the field names I use to identify individuals. Another table has two fields: UserID and Values. UserID is the key that identifies each individual for which there is data. Values contains all the data that should go in each of the fields listed in UserFieldName. So, all emails, work locations, and business units are all in one field.

I would like to create a virtual table in which: a) there is one record for each UserID, b) the data in UserFieldName becomes the fields in the new table, and c) the appropriate values go into their respective field (e.g. all emails go into the email field, etc.). My biggest problem is that I do not know how to convert data into fields.

Please let me know how to do this. Thank you in advance for your help.

Frank

 

[Golom]

When you say that "... Values contains all the data ...", exactly how is the data represented in that one field?

Is it comma (or some other delimiter) separated?

Putting data into a field isn't difficult ... once you have the data. In this case you really don't in the sense that you need a mechanism to extract the data for each field from the "blob" called "Values" that apparently contains all of it.

If you can give us an example of what "Values" contains then we may be able to figure out how to parse the individual field values out of it.

 

[rac2]

Another table has two fields: UserID and Values. UserID is the key that identifies each individual for which there is data. Values contains all the data that should go in each of the fields listed in UserFieldName."

There must be a third field in this table that tells what kind of value is stored in the row.
UserID FieldName Value
101 FirstName Rinaldo
101 Email rinaldog@no.net

If not you have no hope of building the virtual table you need. BTW, in SQL, a virtual table is called a VIEW.

 

[fbarbeite]

VIEW, got it, thank you. I'm learning this as I go along.

Below is a sample of what is in each table. All the fields in each table are presented below.

Table: UserFieldName
UserFieldID UserFieldName Position
1 email 1
2 WorkLocation 2
3 BusinessUnit 1

Table: UserFieldMap
UserID UserFieldID Value
1025 1 ab@acme.com
1025 2 NY
1025 3 Human Resources
1047 1 br@acme.com
1047 2 GA
1047 3 Customer Service

I would like a table or view that will look like this:
UserID email WorkLocation BusinessUnit
1025 ab@acme.com NY Human Resources
1047 br@acme.com GA Customer Service

Thanks again.

Frank

 

[rac2]

As you have may have already guessed, this is accomplished with a JOIN.

SELECT a.UserID,
       a.Value AS "email"
       b.Value AS "WorkLocation"
       c.Value AS "BusinessUnit"
FROM UserFieldMap a
LEFT JOIN UserFieldMap b ON
       b.UserID = a.UserID
       AND b.UserFieldID = 2
LEFT JOIN UserFieldMap c ON
       c.UserID = a.UserID
       AND c.UserFieldID = 3
WHERE a.UserFieldID = 1

The LEFT JOIN ensures that you have a row for every UserID with an email. There may be another table in your database that has all of the users, regardless of what data is recorded about them. That would be a good table to include in this query. As it is written above, a user without an email address row will not be shown.

So-

SELECT u.UserID,
       a.Value AS "email"
       b.Value AS "WorkLocation"
       c.Value AS "BusinessUnit"
FROM Users u
LEFT JOIN UserFieldMap a ON
       a.UserID = u.UserID
       AND b.UserFieldID = 1
LEFT JOIN UserFieldMap b ON
       b.UserID = u.UserID
       AND b.UserFieldID = 2
LEFT JOIN UserFieldMap c ON
       c.UserID = u.UserID
       AND c.UserFieldID = 3

 

[fbarbeite]

rac2,

Thank you for your help. The syntax you provided looks like it it what I need. I have a question about it. Where you use the letters a, b, c, and u, is this where I am supposed to put in the table names? Thanks.

 

[PHV]

They are alias.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[fbarbeite]

So where the letters are, I should put the corresponding table name?

 

[PHV]

NO. Put your table names in lieu of Users and UserFieldMap.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[SQLSister]

LEt me explain a little more about the alias concept. Inthe query, PHV is referncing the same table mmultiple times. This is called a cself join. One of the rules about a self join is that each tim eyou make one, you must give the table an alais (or fake name) so that the database can determine which of the instances of that table that you are referncing. SO the first join to UserFieldMap gets the email records and is named a. SO when you refer tot he field a.Value, you will get email information. And so on with the second join which has worklocation. The refernce to b.Value will give the work information value. Since Value is the field name in both cases, it is the alias that lets the database engine know which one you are refering to in the field list.

Now I want to point out that this code will work very well if you only have one entry per user for each type of value. But if you store multiple email address or some other value, then you will need to do something different to get the values on one line.

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief