tricky sql- really need help

Last Post 08 Jul 2008 04:51 PM by gizzy. 5 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
gizzy
New Member
New Member

--
08 Jul 2008 08:04 AM
To anybody who can help. I know this can be done, I just can't remember how. I am working with a manager and need to write him SQL. He gave me existing SQL statements and then advised on how he wants it displayed.

I have 3 simple statements. Each statement selects 1 column from 3 seperate tables all within the same database. There is no join on any tables. He wants them to be displayed across in 3 columns as opposed to one single row, so using Union will not work

ex

SELECT convert(varchar() colA) as Col1
From tblA
where colA like 'ac%'

SELECT distinct(varchar() colB) as Col2
From vwB
Order by 1

SELECT distinct(varchar() colC) as Col3
From tblA
order by 1

he wants it to display across

ColA ColB ColC

as opposed to
ColA
ColB
ColC





any help would be appreciated. Also the ID that he is using I believe may only have db_datareader rights. So I am limited on cursors, and temp tables etc. Unfortunately this is NOT one of my servers and belongs to another group.

thanx.........
nosepicker
New Member
New Member

--
08 Jul 2008 04:08 PM
If you display the data as 3 columns, how are they supposed to relate to each other? In other words, if the first row has the first value from table #1 as ColA, the first value from table #2 as ColB, and the first value from table #3 as ColC, are those values supposed to be related to each other somehow? If not, what's the point of putting them in the same row? Plus, I'm assuming that some tables will have more values than others. How is the data supposed to be displayed in this case? If your manager just wants 3 lists of data in 3 columns, it seems that it would be easier to just copy and paste the data into a spreadsheet.
gizzy
New Member
New Member

--
08 Jul 2008 04:51 PM
The data does not relate. This is a new project I was assigned to. There is a 3rd party software that he is using which I haven't even seen yet.

Why he wants it displayed this way... sorry I can't answer that. Unfortunately since I do not know how this will interact with the software, or what he does with the resultset, I have to try to give him what he has requested.

I have written him previous SQL and he replied that he tested on a test db and it workes and then he tried in this other software.

I am hoping that it is "do-able". I also have my hands tied with rights. In the long run this db will be taken off the dev server and onto one of my production databases, but until that time this is what I have to work with.

again any help is appreciated

SQLUSA
New Member
New Member

--
08 Jul 2008 11:45 PM
Just to "spill" them vertically, create a #temptable or @tablevariable with 3 columns.

Insert the most populous result set first.

Update it with the other 2 result set.

Probably an additional identity column helpful for reference.

Let us know if helpful.

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQL Server 2005 Training, SSAS, SSIS, SSRS: http://www.sqlusa.com/
Jeff Armstrong
New Member
New Member

--
11 Jul 2008 01:04 PM
Try this

SELECT (SELECT convert(varchar() colA) as Col1 From tblA where colA like 'ac%'),
(SELECT distinct(varchar() colB) as Col2 From vwB Order by 1),
(SELECT distinct(varchar() colC) as Col3 From tblA order by 1)
SwePeso
New Member
New Member

--
15 Jul 2008 05:27 AM
DECLARE @ColA TABLE (RowID INT IDENTITY(1, 1), Col VARCHAR(20))

INSERT @ColA
SELECT ColA
FROM tblA
WHERE ColA LIKE 'ac%'

DECLARE @ColB TABLE (RowID INT IDENTITY(1, 1), Col VARCHAR(20))

INSERT @ColB
SELECT DISTINCT ColB
FROM vwB
ORDER BY ColB

DECLARE @ColC TABLE (RowID INT IDENTITY(1, 1), Col VARCHAR(20))

INSERT @ColC
SELECT ColC
FROM tblA
ORDER BY ColC

SELECT a.Col AS ColA,
b.Col AS ColB,
c.Col AS ColC
FROM @ColA AS a
FULL JOIN @ColB AS b ON b.RowID = a.RowID
FULL JOIN @ColC AS c ON c.RowID = a.RowID
ORDER BY COALESCE(a.RowID, b.RowID, c.RowID)



Acceptable Use Policy
---