extreme help with query of 2 tables into 1 long table

2 tables user id is key
Table (A) 05_Users
user_id | first_name |last_name|title|dept
64|John|Doe|director|cis
65|Jane|Doe|ceo|fina
and
Table(B) 05_Users_Details
user_id | detail_cd | group_cd | detail_value
64|06|awdM0|null
64|07|awdD0|null
64|2005|awdY0|null
64|FreeText|awdTxt0|I enjoy work
64|10|awdM1|null
64|09|awdD1|null
64|2004|awdY1|null
64|FreeText|awdTxt1|still here
64|local|pfmLEVL1|null
64|natial|pfmLEVL1|null
64|aapm|pfmAAPM1|null
64|FreeText|pfmFREE1|profess
65|etc
I'm trying to creat a query that will give me all user information into one
long table with the group_cd as a 'column title' and detail_cd as 'column
value', but if it finds the 'column value' of FreeText then detail_value
should be 'column value'.
So the table would look like this.
user_id | first_name
|last_name|title|dept|awdM0|awdD0|awdY0|awdTxt0|aw dM1|awdD1|awdY1|awdTxt1|pfmLEVL1|pfmLEVL1|pfmAAPM1 |FREE TEXT
64|John|Doe|director|cis|06|07|2005|I enjoy work|10|09|2004|still
here|local|natial|aapm|profess
65|Jane|Doe|ceo|fina etc
Some users have more information than other users and in these cases the
'column value' can be left as blank.
I don't need a webpage, but if it will help, will use.
IF YOU HAVE A BETTER WAY TO GET ALL THE INFORMATION ANY SUGGESTIONS WOULD BE
GREAT!
On Tue, 7 Jun 2005 10:26:02 -0700, BIGLU wrote:
(snip)
>IF YOU HAVE A BETTER WAY TO GET ALL THE INFORMATION ANY SUGGESTIONS WOULD BE
>GREAT!
Hi BIGLU,
First: The format you used to describe your data makes it very hard to
read and understand and almost impossible to reproduce. For future
postings, please include CREATE TABLE and INSERT statements for table
structure and sample data, as described here: www.aspfaq.com/5006.
Second: What you're trying to achieve looks like a pivot, or cross-tab
query. The front end/presentation layer is actually the best place for
that task. If you have to do it on the server, then try if you can adapt
the following to your needs:
SELECT u.UserID,
MAX(CASE WHEN d.DetailCD = 'awdM0' THEN d.detailValue END) AS
awdM0,
MAX(CASE WHEN d.DetailCD = 'awdD0' THEN d.detailValue END) AS
awdD0,
....
FROM Users AS u
INNER JOIN UserDetails AS d
ON d.UserID = u.UserID
GROUP BY u.UserID
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Hugo,
When you say "front end/presentatio layer" can I import and do in access or
excel? If so, can you give me a link where I can do this in any of these? I
know how to export (I think I do.lol), but I'll need help with crosstab, etc.
Or is this something I can do in asp.net? or a third party component?
Thanks
"Hugo Kornelis" wrote:

> On Tue, 7 Jun 2005 10:26:02 -0700, BIGLU wrote:
> (snip)
> Hi BIGLU,
> First: The format you used to describe your data makes it very hard to
> read and understand and almost impossible to reproduce. For future
> postings, please include CREATE TABLE and INSERT statements for table
> structure and sample data, as described here: www.aspfaq.com/5006.
> Second: What you're trying to achieve looks like a pivot, or cross-tab
> query. The front end/presentation layer is actually the best place for
> that task. If you have to do it on the server, then try if you can adapt
> the following to your needs:
> SELECT u.UserID,
> MAX(CASE WHEN d.DetailCD = 'awdM0' THEN d.detailValue END) AS
> awdM0,
> MAX(CASE WHEN d.DetailCD = 'awdD0' THEN d.detailValue END) AS
> awdD0,
> ....
> FROM Users AS u
> INNER JOIN UserDetails AS d
> ON d.UserID = u.UserID
> GROUP BY u.UserID
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
|||On Thu, 9 Jun 2005 09:42:06 -0700, LU wrote:

>Hugo,
>When you say "front end/presentatio layer" can I import and do in access or
>excel?
Hi LU,
I must admit that I have little expertise with respect toi front end
applications. But as far as I know, Access has some builtin
functionality to create a cross-tab table (look up "TRANSFORM" and
"PIVOT" in the online help, or use the crosstab query wizard). And Excel
can do crosstab reports as well.

>Or is this something I can do in asp.net?
Probably, but you'd better ask in a group for asp.net! <g>

>or a third party component?
Some third party applications that might help you generate the crosstab
at the server (though I still recommend against it!) may be found near
the end of this page: http://www.aspfaq.com/show.asp?id=2462
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)