Difficult query, multiple columns

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Difficult query, multiple columns

Author

Topic

Humate
Posting Yak Master

101 Posts

Posted - 2007-11-27 : 17:12:13

Hi All,

I have a query that I have been trying to get to work in SQL for some weeks without success.

I have one table containing client records, and another much larger table that lists every appointment for every client.

I want to produce my data in the following order.

ClientRef--------StartDate--------1------------2-----------3-------
1234---------01/01/2007----02/15/2007---04/30/2007---07/03/2007

I get client ref and start date from my first table "clientdata". All appointments are stored in the 2nd table, with clientref, appointment type (ie. appointment number 1,2,3) with the appointment date.

I want the results to display each client ref by rows, and each appointment type in the columns, returning the appointment date in the relevant column(if the appointment exists).

Any advice much appreciated.

Regards
Humate

Will H
Yak Posting Veteran

56 Posts

Posted - 2007-11-27 : 17:24:53

Do all clients have only 3 appointments max?

---------------------------------------------------------
SSRS Kills Kittens.

Humate
Posting Yak Master

101 Posts

Posted - 2007-11-27 : 17:28:29

Hi Will,

Max appointment type currently goes upto 12 so far, not all have this many.

Thanks
Humate

Will H
Yak Posting Veteran

56 Posts

Posted - 2007-11-27 : 17:34:01

Well, this will do it up til 3, but if you can't control the # of appointments it will get hard because you have to keep adding subqueries.. Also, play around with the join type to show nulls when a client didn't have 3 apps scheduled. Presentation like this should really be done in the UI and not the data layer.

SELECT c.ClientRef, c.StartDate, a1.AppDate c1 , a2.AppDate c2, a3.AppDate c3
FROM ClientData c
JOIN (
SELECT ClientRef, AppDate
FROM App
WHERE AppType = 1
) a1 on a1.ClientRef = c.ClientRef
JOIN (
SELECT ClientRef, AppDate
FROM App
WHERE AppType = 2
) a2 on a2.ClientRef = c.ClientRef
JOIN (
SELECT ClientRef, AppDate
FROM App
WHERE AppType = 3
) a3 on a3.ClientRef = c.ClientRef

---------------------------------------------------------
SSRS Kills Kittens.

Humate
Posting Yak Master

101 Posts

Posted - 2007-11-27 : 17:40:50

That is great, I will try the query now.

I am a newbie to SQL just doing some queries through SSMS - is there a better way to present the data?

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-11-27 : 18:01:45

Here's some code on making a dynamic pivot. It's a little overkill, but it should work well

The only part you need to change is the query tbl1 and tbl2 names, The line where this change needs to be made is marked in green Below. Change the Purple part so it pulls from the correct tables.


CREATE PROCEDURE SPROC_YourProcedure
AS

SET NOCOUNT ON

CREATE TABLE #Aggregates
             (
              RowText1 VARCHAR(50),
			  RowText2 VARCHAR(50),
              ColumnText VARCHAR(50),
              CellData Datetime
             )

INSERT INTO #Aggregates
            (
             RowText1,
			 RowText2,
             ColumnText,
             CellData
            )
--Change The TBL names to be correct

SELECT      a.ClientRef,
            a.StartDate,
            b.AppointmentType,
			b.AppointmentDate
FROM        tbl1 a
INNER JOIN  tbl2 b
ON			a.ClientRef = b.ClientRef


CREATE UNIQUE INDEX IX_Aggregates ON #Aggregates (RowText1,RowText2,ColumnText, CellData)
 
CREATE TABLE #Columns
            (
             ColumnIndex INT IDENTITY (0, 1),
             ColumnText VARCHAR(50)
             )
 
INSERT INTO     #Columns
                (
                 ColumnText
                )
SELECT DISTINCT ColumnText
FROM           #Aggregates with (INDEX(IX_Aggregates), NOLOCK)
ORDER BY       ColumnText
 
CREATE UNIQUE INDEX IX_Columns ON #Columns (ColumnIndex, ColumnText)
 
CREATE TABLE #Rows 
             (
              RowText1 VARCHAR(50),
			  RowText2 VARCHAR(50)
             )
 
INSERT INTO    #Rows
                (
                 RowText1,RowText2
                )

SELECT DISTINCT RowText1,RowText2
FROM            #Aggregates with (INDEX(IX_Aggregates), NOLOCK)
 
CREATE UNIQUE INDEX IX_Rows ON #Rows (RowText1,RowText2)
 
DECLARE     @ColumnIndex INT,
            @MaxColumnIndex INT,
            @ColumnText VARCHAR(50),
            @SQL VARCHAR(1000)
 
SELECT      @ColumnIndex = 0,
            @MaxColumnIndex = MAX(ColumnIndex)
FROM        #Columns
 

WHILE @ColumnIndex <= @MaxColumnIndex
   BEGIN
      SELECT     @ColumnText = ColumnText
      FROM       #Columns
      WHERE      ColumnIndex = @ColumnIndex
 
      SELECT     @SQL = 'ALTER TABLE #Rows ADD ' + QUOTENAME(@ColumnText) + ' DATETIME NULL DEFAULT ''01/01/1900''
      EXEC       (@SQL)
 
      SELECT     @SQL = 'UPDATE #Rows SET ' + QUOTENAME(@ColumnText) + ' = #Aggregates.CellData
                         FROM #Aggregates (INDEX(IX_Aggregates), NOLOCK)
								    , #Columns (INDEX(IX_Columns), NOLOCK) 
								 WHERE #Rows.RowText1 = #Aggregates.RowText1 
								 AND #Rows.RowText2 = #Aggregates.RowText2
								 AND #Columns.ColumnText = #Aggregates.ColumnText 
								 AND #Columns.ColumnIndex = ' + CAST(@ColumnIndex AS VARCHAR(12))
      EXEC       (@SQL)
 
      SELECT  @ColumnIndex = @ColumnIndex + 1 
   END

DROP TABLE #Columns
DROP TABLE #Aggregates

SELECT     #Rows.*
FROM        #Rows
ORDER BY   #Rows.RowText1,#Rows.RowText2

DROP TABLE  #Rows

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-11-27 : 18:12:16

To answer your question about whether there is a better way to do this, the answer is YES, use a reporting software (SQL REPORTING SERVICES, Crystal Reports, etc).

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources