Group records into rows using date

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 2000 Forums

SQL Server Development (2000)

Group records into rows using date

Author

Topic

JezEling
Starting Member

4 Posts

Posted - 2005-08-31 : 11:31:46

Hi All,

I have been trying to create a query that will all me to extract data from a table and group the records retrieved into a single row based on date, for example: -

Data
1/1/2005 6
1/1/2005 10
1/1/2005 20
1/1/2005 30
1/2/2005 5
1/2/2005 6
1/3/2005 19

Required Output from query
1/1/2005 6 10 20 30
1/2/2005 6 6
1/3/2005 19

Can anyone provide me with any assistance on this matter?

Many thanks in advance

Jez

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2005-08-31 : 12:10:07

This post might help.

---------------------------
EmeraldCityDomains.com

magesh
Starting Member

23 Posts

Posted - 2005-09-01 : 02:40:16

Hi,

you can do that using UDF..
heres is an example with DDL and UDF

---DDL----
CREATE TABLE [UDFTBL] (
[DateCol] [datetime] NOT NULL ,
[IDCol] [int] NOT NULL ,
CONSTRAINT [PK_UDFTBL] PRIMARY KEY CLUSTERED
(
[DateCol],
[IDCol]
) ON [PRIMARY]
) ON [PRIMARY]
GO

---UDF-----

CREATE FUNCTION SPACEID(@DATECOL DATETIME)
RETURNS VARCHAR(1000)
AS
BEGIN
DECLARE @OUTPUT VARCHAR(100)

SET @OUTPUT=''

SELECT @OUTPUT = CASE @OUTPUT
WHEN '' THEN CAST(IDCol AS VARCHAR(10))
ELSE @OUTPUT + ' ' + CAST(IDCol AS VARCHAR(10))
END
FROM UDFTBL WHERE DateCol = @DATECOL
ORDER BY IDCOL
RETURN @OUTPUT
END
GO

--------

select DISTINCT DateCol,dbo.SPACEID(DateCol) AS IDVAL
FROM UDFTBL

regards
Magesh

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-01 : 03:01:43

In that topic given by Ajarn, I provided the link that does this by using Function

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources