March 19, 2012 at 12:41 pm
This seems like it should be simple, but I have not found the solution. The images attached are vital to understand this situation as I'm really not sure how to describe what I have versus what I need; I tried to include them in the body of the posting, but they are not showing.
I have a table with data that has multiple records per ID, with various columns containing the necessary data, as shown in SampleData.png. I need to write a query that will return the data as shown in SampleData2.png, and join another table to return additional fields. The distinct value in the CONTACT column is what I need to get the associated records in the second table; not the ID field. I have tried GROUP BY and DISTINCT to get the data returned just from the first table for the way I need but have not had any success. Any suggestions would be appreciated.
**** UPDATE ****
I just realized the attached images are not 100% accurate. The ID field is in reality of course unique as the key field; it is completely random, unassociated to the rest of the fields in the record. The repeating value in the image (the ID column) is the field I need to use to join to the second table as it will be the same for all the records in that 'group'.
Sorry for any confusion this presents.
March 19, 2012 at 1:23 pm
you can most likely use the coalesce function. For a more detailed and tested solution please see the link in my signature on the most helpful way to ask questions.
The table definitions and test data will help greatly increase the helpfulness and accuracy of the responses you receive.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
March 19, 2012 at 1:28 pm
If you really want some help with this issue please read the first link in my signature for best practices on asking questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 19, 2012 at 2:43 pm
My apologies. I was simply frustrated at a couple other forums while following their requests, and... no excuses, right?!
So is this what you are requesting (below)? I have the CREATE statements for two tables: the first is an example of the existing data structure, and the second is how I need to have the data after I query the table.
--If the test table already exists, drop it
IF OBJECT_ID('my_test','U') IS NOT NULL
DROP TABLE my_test
/*
Create the test table with data as it appears currently with
*/
create table my_test
(
GUIDINT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
ContactNVARCHAR(10),
HistoryNVARCHAR(10),
URL1NVARCHAR(25),
URL2NVARCHAR(25),
URL3NVARCHAR(25)
)
--All Inserts into the IDENTITY column
SET IDENTITY_INSERT my_test ON
INSERT INTO my_test
(GUID, Contact, History, URL1, URL2, URL3)
SELECT '1', '101', NULL, NULL, 'www.abc.com', NULL UNION ALL
SELECT '2', '101', 'BM004', NULL, NULL, 'www.112233.com' UNION ALL
SELECT '3', '101', NULL, 'www.smsmsmsms.com', NULL, NULL UNION ALL
SELECT '4', '101', NULL, NULL, NULL, NULL UNION ALL
SELECT '5', '202', NULL, NULL, NULL, NULL UNION ALL
SELECT '6', '202', NULL, 'www.mymy.org', NULL, NULL UNION ALL
SELECT '7', '202', 'BM900', NULL, NULL, NULL UNION ALL
SELECT '8', '202', NULL, NULL, NULL, 'www.aaabbb.com' UNION ALL
SELECT '9', '202', NULL, NULL, 'www.ooooo.com', NULL
SET IDENTITY_INSERT my_test OFF
--If the results table already exists, drop it
IF OBJECT_ID('my_results','U') IS NOT NULL
DROP TABLE my_results
--Create the results table the way I would like to see the results from the first (my_test) table with
create table my_results
(
GUIDINT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
ContactNVARCHAR(10),
HistoryNVARCHAR(10),
URL1NVARCHAR(25),
URL2NVARCHAR(25),
URL3NVARCHAR(25)
)
--All Inserts into the IDENTITY column
SET IDENTITY_INSERT my_results ON
INSERT INTO my_results
(GUID, Contact, History, URL1, URL2, URL3)
SELECT '1', '101', 'BM004', 'www.abc.com', 'www.112233.com', 'www.myplace.com' UNION ALL
SELECT '2', '202', 'BM900', 'www.mymy.org', 'www.ooooo.com', 'www.aaabbb.com'
SET IDENTITY_INSERT my_results OFF
March 19, 2012 at 3:01 pm
thats exactly what we needed. Thanks
This may work but will break (kinda) if there is more than 1 entry in each column for each contact.
SELECT m.Contact, MAX(m.History), MAX(m.URL1), MAX(m.URL2), MAX(m.URL3)
FROM my_test m
GROUP BY m.Contact
On the small result set it returns correctly but does have the problems mentioned. the MAX gets rid of the nulls. now the table design really needs work to get rid of all the nulls.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
March 19, 2012 at 3:07 pm
Was just about to post almost exactly the same response. 😛 Hopefully that will work.
One thing you should consider. I know this is sample ddl but you should be careful about column names and don't use reserved words. Perhaps even more importantly don't use column names that imply a certain datatype and in fact are something different. In reality GUID is not a reserved word but it does highlight in blue like a reserved word...not really sure why.
http://msdn.microsoft.com/en-us/library/ms189822.aspx
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 19, 2012 at 3:27 pm
Appreciate the suggestion, but yes, on the actual table it throws an error:
"Each GROUP BY expression must contain at least one column that is not an outer reference"
March 19, 2012 at 3:30 pm
What is the actual query you are running. This is probably not data related.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 20, 2012 at 12:06 am
hi ,
this may be correct or not i dont know any way try this
select URL1.*,URL23.URL2,URL23.URL3 from(select history.Contact,history,URL1.URL1 from(select Contact ,history from my_test where history is not null )as history
Full join
(select Contact,URL1 from my_test where URL1 is not null) URL1
on history.contact=URL1.Contact) as URL1
Full join
(select URL2.Contact,URL2,URL3 from (select Contact,URL2 from my_test where URL2 is not null)as URL2
Full join
(select Contact,URL3 from my_test where URL3 is not null)as URL3
on URL2.Contact=URL3.Contact )as URL23
on URL1.Contact=URL23.Contact
Regards
Guru
March 20, 2012 at 5:03 am
The actual query is/was:
SELECT
fld_3003 AS 'Contact'
,MAX(fld_4101) AS 'History'
,MAX(fld_3900) AS 'URL1'
,MAX(fld_3901) AS 'URL2'
,MAX(fld_3902) AS 'URL3'
FROM
cont_hist_t100
GROUP BY
'Contact'
Once I used just the actual column names and removed the "AS '...' " it worked! Thanks so much for all the help!
March 20, 2012 at 5:13 am
There is no problem with the alias however you need to use the actual column name I'm the group by and not the column alias. That's where your error was coming from
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply