Complex (??) Query

Question

Complex (??) Query

darkins

Newbie

Points: 8
More actions
February 26, 2008 at 7:29 am

#62745

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.

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

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

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

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://www.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?

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 4

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

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 5

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?

Anipaul SSC-Insane Points: 24681 More actions · Answer 6

Yes Matt is again bang on target.

XML Path is introduced only in 2005. You haven't specified the version you are using.

Jack Corbett SSC Guru Points: 184394 More actions · Answer 7

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

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 8

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jack Corbett SSC Guru Points: 184394 More actions · Answer 9

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

darkins Newbie Points: 8 More actions · Answer 11

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

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 12

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?

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 13

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)