Web Development

Ask something
Question Locked

What's the right SQL Statement

0 helpful
13 replies

by jeffreyalejo

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I got one(1) table with the following data:

Table Name: MyTable

ID Date Name
==============================
1 2008-08-31 James
2 2008-08-31 Jack
3 2008-08-31 Gregory
4 2008-08-29 James
5 2008-08-29 Jack
==============================

Now, how can I make it look like this:

Name Date1 Date2
=================================
James 2008-08-31 2008-08-29
Jack 2008-08-31 2008-08-29
Gregory 2008-08-31
=================================

Basically, I'm grouping it by the field Name.

13 replies

by thumbsup2

We need more information on what you're trying to do. Is this for a report or for restructuring the database?

by jeffreyalejo

I will transfer the "output" of the query to another table, therefore the "output" will be used for another table.

But I can't make the appropriate query to have the desired "output".

This is the table where the query is to be taken out:

ID Date Name
==============================
1 2008-08-31 James
2 2008-08-31 Jack
4 2008-08-29 James
5 2008-08-29 Jack
==============================

...., and this is the table where the "output" from the query is to be used:

Name Date1 Date2
=================================
James 2008-08-31 2008-08-29
Jack 2008-08-31 2008-08-29
=================================

by bizzo

What happened to Gregory?

Did he get sacked?
That's a shame, he was such a nice guy too.

by hwkranger

There are a couple of questions.

1: Is there a maximium of 2 dates?
If So, You'll want to do the following
SELECT NAME, MAX(DATE), MIN(DATE)
FROM TABLE
GROUP BY NAME

2: If there is not a maxmium of 2 dates and you want to see the all the dates (date1, date2,.. daten) you'll need to do a cross tab query.
2a: If you know the maxmium number of dates per person, you can use Case statements to pivot the dates (or pivot if you're ingenius)
2b: If you do not know the maxmium number of dates, then you'll need a dynamic crosstabbing query to cross tab your select statement.

Hope this helps.

R. Vollen

by jeffreyalejo

I think the cross tab query is the solution. In my example I got 2 different Names with two different Dates.

There are two records for James with different Dates:

James 2008-08-29
James 2008-08-31

..and two records for Jack with different Dates as well:

Jack 2008-08-29
Jack 2008-08-31

(*Note: I removed Gregory's record)

What I want is to display the record like this:

James 2008-08-29 2008-08-31
Jack 2008-08-29 2008-08-31

I want to combine the records with the same Name and place the different Dates in separate columns.

Thanks for the reply.

by praspam

select name,date,null from mytable

union
select null,null,date from mytable

group by name

by jeffreyalejo

I've tried your query and the result is like this:

James 2008-08-29
James 2008-08-31
Jack 2008-08-29
Jack 2008-08-31

(*Note: I removed Gregory's record)

What I want is to display the record like this:

James 2008-08-29 2008-08-31
Jack 2008-08-29 2008-08-31

I want to combine the records with the same Name and place the different Dates in separate columns.

Thanks for the reply.

by thumbsup2

What you want to do is select all of the records needed from the table, put them in a temporary holding spot (array) using your programming. Then, again using programming, step through that array using the contents to build a new table...

You can't extract records from a table and put them into a new table which is structured differently using just ONE statement.

by jeffreyalejo

Please give me a sample of how to do it. It's obvious that I'm a newbie..huhuhu..Thanks again!

by praspam

alter FUNCTION dbo.ufn_FindReports (@sName varchar(50))
RETURNS @retFindReports TABLE
(
sdate varchar(255)
)
AS
BEGIN
declare @sdate varchar(255);
WITH DateList(sdate) AS
(
SELECT cast(date as varchar(12)) FROM mytable where sname=@sName
)
INSERT @retFindReports
SELECT sdate fROM DateList
RETURN
END
GO

declare @sdate varchar(255)
SELECT @sdate = COALESCE(@sdate + ', ', '') +
cast(sdate as varchar(12))
FROM dbo.ufn_FindReports('James')

select distinct (select @sdate),sname from mytable where sname='James'

This discussion is closed to new replies.