February 26, 2008 at 7:29 am
Hey,
I am not the greatest TSQL guy (not even the not so greatest) so here is a question.
I have two tables. One has 1 "A" Record (table a) and the other has 12 "B" records (table b). The tables are keyed and linked on one field. I want to query and get back the one record from table a, the 12 records from table b, but display them on ONE ROW.
ie.
February 26, 2008 at 7:48 am
I would definitely call this a complex query as basically you want to "pivot" the data and SQL 7, 2000 do not do this natively. In Standard SQL a join returns a set of records like:
Column1 Column2
-------- --------
a b1
a b2
a b3
There are a few ways I know of to accomplish what you want:
Create a temporary table with all the columns you want in the row, do an insert into the first column and then updates into the rest
Use a union query for all the queries, but then you need to have criteria to limit your results to 1 row for each union like this:
Select
A.column as colA,
B.column as b1,
Null as b2,
Null as b3,
....
From
tableA A Join
tableB B On
A.primary_key = B.foreign_key
Where
B.value = 'Value1'
Union
Select
A.column as colA,
Null as b1,
B.column as b2,
Null as b3,
....
From
tableA A Join
tableB B On
A.primary_key = B.foreign_key
Where
B.value = 'Value2'
etc...
You can also do derived tables and/or correlated subqueries. I am sure there are others who have a much cleaner and simpler way that they do this.
In actuality the best way to handle this since you use the word "display" is to "pivot" the data in your UI. SQL is not a display language it is a query language and display issues whould be handled in a UI.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 26, 2008 at 7:48 am
Sounds like something you would want your app, rather than SQL Server, to do. Put please will you post table definitions in the form of CREATE TABLE statements and sample data in the form of INSERT statements so that we can have a look.
John
February 26, 2008 at 7:49 am
Considering the total lack of detail of info given, I guess the answer is - "yes - under the right conditions - that certainly is possible". You should probably spend some time looking up "PIVOT SQL2000" in a google search and you will find a lot of perfectly acceptable solutions.
If you're still stuck after that - take a read through this. It's a "help us help you" article - it should give you soem good ideas on what kind of info we can use to give you some concrete help:
http://qa.sqlservercentral.com/articles/Best+Practices/61537/[/url]
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 26, 2008 at 7:56 am
Or, if you need the data from the second table as a list, you can do that too on SQL Server 2005 like:
CREATE TABLE A ( a INT, b INT )
CREATE TABLE B ( b INT, c VARCHAR(100) )
INSERT INTO A VALUES ( 1, 1 )
INSERT INTO A VALUES ( 2, 2 )
INSERT INTO B VALUES ( 1, 'AAA' )
INSERT INTO B VALUES ( 1, 'BBB' )
INSERT INTO B VALUES ( 1, 'CCC' )
INSERT INTO B VALUES ( 2, 'aa' )
INSERT INTO B VALUES ( 2, 'bb' )
SELECT A.a
, STUFF(( SELECT DISTINCT TOP 100 PERCENT
',' + t2.c
FROM B AS t2
WHERE A.b = t2.b
ORDER BY ',' + t2.c
FOR
XML PATH('')
), 1, 1, '')
FROM A
This will return
a
----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 AAA,BBB,CCC
2 aa,bb
If you need separate columns, go with the advise of the others, on 2005 pivots.
Regards,
Andras
February 26, 2008 at 7:59 am
Assuming you're on 2000 - unfortunately Andras' answer will NOT work (XML PATH is a 2005 feature...)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 26, 2008 at 8:37 am
Yes Matt is again bang on target.
XML Path is introduced only in 2005. You haven't specified the version you are using.
February 26, 2008 at 9:10 am
I always assume that the OP posted in the correct forum and this is a SQL 7, 2000 forum. So I try to answer using methods available on that platform.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 26, 2008 at 9:16 am
darkins (2/26/2008)
Hey,I am not the greatest TSQL guy (not even the not so greatest) so here is a question.
I have two tables. One has 1 "A" Record (table a) and the other has 12 "B" records (table b). The tables are keyed and linked on one field. I want to query and get back the one record from table a, the 12 records from table b, but display them on ONE ROW.
ie.
This is actually pretty easy to do even in SQL Server 7 or 2000... what I need to know is do you want any kind of delimiter between the 12 items on the 1 row or just a space?
--Jeff Moden
February 26, 2008 at 11:05 am
Jeff Moden (2/26/2008)
darkins (2/26/2008)
Hey,I am not the greatest TSQL guy (not even the not so greatest) so here is a question.
I have two tables. One has 1 "A" Record (table a) and the other has 12 "B" records (table b). The tables are keyed and linked on one field. I want to query and get back the one record from table a, the 12 records from table b, but display them on ONE ROW.
ie.
This is actually pretty easy to do even in SQL Server 7 or 2000... what I need to know is do you want any kind of delimiter between the 12 items on the 1 row or just a space?
Jeff,
Can you post how you would solve this? I don't care the delimiter I just want to see the easy solution. Since I have not found one yet. Although usually I am looking for a column for every row returned not a single delimited column, which I think you are proposing.
Thanks,
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 26, 2008 at 11:21 am
I'm guessing Jeff has something like this in mind. It's not a pivot, it's a concatenation and all the values will appear in one column.
DECLARE @ConcatB VARCHAR(8000)
SET @ConcatB = ''
SELECT @ConcatB = @ConcatB + B + ',' FROM TableB
SELECT A, @ConcatB FROM TableA
Since you stated that TableA has only one row, the above will work. If it has more than one row, you'll have to use one of Jeff's fancy concatenation functions, which he'll probably be along very shortly to post.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 26, 2008 at 1:20 pm
Jeff,
I am not concerned with a delimiter. Yes I am sql 2000 (this forum). I would like to see both delimeter and non if it isnt too hard.
Dan
February 26, 2008 at 1:29 pm
You already have one example (look at GilaMonster's syntax above as to the "delimited" example).
As to the pivoting example - Here's as good an example as any.
http://www.daymap.net/blog/?p=6
Posting your table specifics would be required if you don't see how this would apply to your scenario.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 26, 2008 at 5:33 pm
Jack Corbett (2/26/2008)
Jeff,Can you post how you would solve this? I don't care the delimiter I just want to see the easy solution. Since I have not found one yet. Although usually I am looking for a column for every row returned not a single delimited column, which I think you are proposing.
Thanks,
Sure... and what Gail said...
GilaMonster (2/26/2008)
I'm guessing Jeff has something like this in mind. It's not a pivot, it's a concatenation and all the values will appear in one column.
... is pretty much spot on... as usual, the comments in the code should say it all...
--===== Use a database where no harm can come
USE TempDB
--===================================================================
-- Create and populate the test tables.
-- THIS IS NOT PART OF THE SOLUTION.
--===================================================================
CREATE TABLE TableA (ColA INT, ColB INT)
CREATE TABLE TableB (ColB INT, ColC VARCHAR(100) )
INSERT INTO TableA
(ColA, ColB)
SELECT 1,3 UNION ALL
SELECT 2,4
INSERT INTO TableB
(ColB, ColC)
SELECT 3, 'AAA' UNION ALL
SELECT 3, 'BBB' UNION ALL
SELECT 3, 'CCC' UNION ALL
SELECT 4, 'aa' UNION ALL
SELECT 4, 'bb'
GO
--===================================================================
-- Create a concatenation function to do the heavy lifting.
--===================================================================
CREATE FUNCTION dbo.fnConcatBC
(@B INT)
RETURNS VARCHAR(8000)
AS
BEGIN
--===== Declare a variable to do the concatenation in
DECLARE @Return VARCHAR(8000)
--===== Set based "loop" concatenates the data into a variable
-- The ISNULL makes it possible to suppress the final delimiter
-- (Suppresses the first delimiter, actually)
SELECT @Return = ISNULL(@Return + ',', '') + ColC
FROM TableB
WHERE ColB = @b-2
ORDER BY ColC
RETURN @Return
END
GO
--===================================================================
-- Use the new function to produce what I think is the desired
-- output. Note that the function produces "," as the delimiter
-- in this case and that the trailing delimiter is suppressed.
--===================================================================
SELECT a.ColA,
a.ColB,
dbo.fnConcatBC(a.ColB) AS ConcatenatedDataFromC
FROM TableA a
GO
--===================================================================
-- Housekeeping
--===================================================================
DROP TABLE TableA, TableB
DROP FUNCTION dbo.fnConcatBC
That assumes, of course, that Dan (the OP) can use that format. If he needs it to really be separate columns, then we'll need to resort to the PITA of dynamic SQL in the form of a crosstab.
--Jeff Moden
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply