If you know how many columns "across" you'll have, this is a fairly simple query.
SELECT
u.user_id,
MAX( (CASE at.xf_name WHEN 'XF_1' then '' else NULL end) + uxf.val ) as XF_1,
MAX( (CASE at.xf_name WHEN 'XF_2' then '' else NULL end) + uxf.val ) as XF_2,
...
MAX( (CASE at.xf_name WHEN 'XF_n' then '' else NULL end) + uxf.val ) as XF_n
FROM
users u,
attribs at,
users_attribs uxf
WHERE
uxf.user_id = u.user_id AND
uxf.xf_id = at.xf_id
GROUP BY
u.user_id ;
This assumes that (NULL + 'string') evaluates to NULL, whereas (NULL || 'string') evaluates to 'string'.
If you have a dynamic number of columns across, then this solution doesn't apply.
--
Paul Horan
Sr. Architect
VCI Springfield, Mass
www.vcisolutions.com
"Preston Landers" <pibble@yahoo.com> wrote in message news:1136f745.0409011656.2c791d98@posting.google.c om...[color=blue]
> Hello all.
>
> I am trying to write a query that "just" switches some data around so
> it is shown in a slightly different format. I am already able to do
> what I want in Oracle 8i, but I am having trouble making it work in
> SQL Server 2000. I am not a database newbie, but I can't seem to
> figure this one out so I am turning to the newsgroup. I am thinking
> that some of the SQL Gurus out there have done this very thing a
> thousand times before and the answer will be obvious to them.
>
> This message is pretty long but hopefully gives you enough information
> to replicate the issue.
>
> There are 3 tables involved in my scenario. Potentially a lot more in
> the real application, but I'm trying to keep this example as simple as
> possible.
>
> In my database I have many "things". Let's call them "User Records"
> (table: users) for this example. My app allows the customer to create
> any number of custom "Extra Fields" (XF's) for a given User Record.
> The Extra Field definitions are stored in a table which we can call
> attribs. The actual XF values for a given user record are stored in a
> third table, let's call it users_attribs.
>
> users_attribs will look something like this (actual DDL below.)
>
> UserID | ExtraFieldID | Value
> --------------------------------------
> User_1 | XF_1 | ham
> User_1 | XF_2 | eggs
> User_2 | XF_1 | bacon
> User_2 | XF_2 | cheese
> User_3 | XF_2 | onions
>
> The end result is that I want a SQL query that returns something like
> this:
>
> UserID | XF_1 | XF_2
> -------------------------------------
> User_1 | ham | eggs
> User_2 | bacon | cheese
> User_3 | NULL | onions
>
> Potentially there would be one column for each extra field definition.
> One interesting question is how to get a dynamic number of columns to
> show up in results, (so new XF's show up automatically) but I'm not
> worried about that for now. Assume I will hard-code a specific set of
> extra fields into my query.
>
> The key here is that all users must show up in the final result EVEN
> IF they don't have some extra field value defined. Since User_3 in
> the example above doesn't have an XF_1 record, we see a NULL in that
> column in the final result.
>
> With Oracle I am able to accomplish this via an Outer Join, and I know
> SQL Server supports Outer Joins, but I can't seem to make it work. In
> ever version I have tried so far, if any user is missing any extra
> field value, the entire row for the user goes "missing", and that is
> my problem.
>
> It seems like one possible solution would be to just go ahead and
> populate the users_attribs table with a NULL value for that
> combination of user ID and extra field ID, basically adding a new row
> like this:
>
> UserID | ExtraFieldID | Value
> --------------------------------------
> User_3 | XF_1 | NULL
>
> I would like to avoid that if possible, for a number of reasons,
> particularly the question of *when* that NULL would be added. I don't
> want my report to touch the database and add stuff at reporting time
> if at all possible. In Oracle, I seemingly don't have to, and I want
> to get to that point on SQL Server.
>
> So, here is some specific DDL to recreate this scenario:
>
>
> CREATE TABLE users (user_id varchar(60), username varchar(60));
>
> -- Extra Field (attribs) definitions
> CREATE TABLE attribs (xf_id varchar(60), xf_name varchar(60));
>
> -- Extra Field values for Users
> CREATE TABLE users_attribs (user_id varchar(60), xf_id varchar(60),
> val varchar(60));
>
> -- populate the sample tables
>
> -- sample User recs
> INSERT INTO users VALUES ('U_1', 'John Smith');
> INSERT INTO users VALUES ('U_2', 'Mary Rogers');
>
> -- sample extra field definitions
> INSERT INTO attribs VALUES ('XF_1', 'Extra Field 1');
> INSERT INTO attribs VALUES ('XF_2', 'Extra Field 2');
> INSERT INTO attribs VALUES ('XF_3', 'Extra Field 3');
>
> -- sample values for User Extra Fields (XF's)
> -- U_1 ("John Smith") has complete values for each XF
> INSERT INTO users_attribs VALUES ('U_1', 'XF_1', 'XF_1 value for
> U_1');
> INSERT INTO users_attribs VALUES ('U_1', 'XF_2', 'XF_2 value for
> U_1');
> INSERT INTO users_attribs VALUES ('U_1', 'XF_3', 'XF_3 value for
> U_1');
>
> -- U_2 ("Mary Rogers") only has one value, missing the other two..
> INSERT INTO users_attribs VALUES ('U_2', 'XF_2', 'XF_2 value for
> U_2');
>
>
> Now, I can get what I want on Oracle, provided that I define an new
> view that joins the three tables together, then do a separate query on
> that view that does an outer join. I could dispense with the view,
> but I don't want to hard-code the XF ID's into the query. I am fine
> with hardcoding the XF names, though. (Long story.)
>
> -- Create a User Extra Field view that joins Users
> -- extra field definitons (attribs)
> -- and values (users_attribs.)
>
> CREATE VIEW u_xf_view AS
> SELECT u.user_id, at.xf_name, uxf.val
> FROM
> users u,
> attribs at,
> users_attribs uxf
> WHERE
> uxf.user_id = u.user_id AND
> uxf.xf_id = at.xf_id
>
>
> -- Oracle-only outer join syntax works if you use the view:
>
> SELECT
> u.username as "User Name",
> uxf1.val as "Extra Field 1 Value",
> uxf2.val as "Extra Field 2 Value",
> uxf3.val as "Extra Field 3 Value"
>
> FROM
> users t,
> u_xf_view uxf1,
> u_xf_view uxf2,
> u_xf_view uxf3
>
> WHERE
> uxf1.user_id(+) = t.user_id AND
> uxf1.xf_name(+) = 'Extra Field 1' AND
>
> uxf2.user_id(+) = t.user_id AND
> uxf2.xf_name(+) = 'Extra Field 2' AND
>
> uxf3.user_id(+) = t.user_id AND
> uxf3.xf_name(+) = 'Extra Field 3'
> ;
>
> -- RESULTS (correct):
>
> User Name Extra Field 1 Value Extra Field 2 Value Extra
> Field 3 Value
> ------------- ------------------------ ------------------------
> ------------------------
> John Smith XF_1 value for U_1 XF_2 value for U_1 XF_3
> value for U_1
> Mary Rogers NULL XF_2 value for U_2 NULL
>
> 2 Row(s)
>
> So far I have not been able to get the equivalent result in SQL
> Server. Like I said, I am really hoping to avoid populating those
> NULL values. Can anything think of a way to replicate Oracle's
> behavior here? I have tried a number of variations on the ANSI join
> syntax instead of Oracle's (+) operator, but everything I tried so far
> has only yielded a row when ALL extra fields are populated (or even
> worse behavior.)
>
> I greatly appreciate any assitance you may be able to give. I would be
> happy to provide any additional information if I forgot to mention
> something important. I apologize in advance for any broken / wrapped
> lines. Thank you for taking the time to read this.
>
> I'm going to be out of town for the next week or so, so I won't check
> for a response until then, but as soon as I get back home I will check
> back in the newsgroup.
>
> Thank you!!
>
> Preston Landers
>
> pibble (at) yahoo (dot) com[/color]
