|
Home > Archive > MS SQL Server MSEQ > June 2005 > extreme help with query of 2 tables into 1 long table
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
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|dep
t
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|nu
ll
64|natial|pfmLEVL1|n
ull
64|aapm|pfmAAPM1|nul
l
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|dep
t|awdM0|awdD0|awdY0|
awdTxt0|awdM1|awdD1|
awdY1|awdTxt1|pfmLEV
L1|pfmLEVL1|pfmAAPM1
|FREE TEXT
64|John|Doe|director
|cis|06|07|2005|I enjoy work|10|09|2004|stil
l
here|local|natial|aa
pm|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!
| |
| Hugo Kornelis 2005-06-08, 3:23 am |
| 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)
>
| |
| Hugo Kornelis 2005-06-10, 8:23 pm |
| 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)
|
|
|
|
|