Summarising data - listing - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Summarising data - listing

Author

Topic

newbie007
Starting Member

37 Posts

Posted - 2011-04-12 : 10:42:17

I have data in the attached format which lists equipment owned by a person on a particular date. Homeno+name is a unique field.
http://img97.imageshack.us/i/sqlteamdata.png/

I need the output to summarise the equipments owned by a Homeno+name on a particular date.
If similar equipments are owned by different Homeno+name they should be reported in the same order
For eg, here 2B owns same equipment as 1A but entered in a diff order. While summarising the order should be similar
What query will get me these results ? Im using SQL Server 2000.
I have tried using group by but with no success

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2011-04-13 : 03:15:21

Try this -


CREATE TABLE #Test ( HomeNo INT, Name VARCHAR(100), Date DATETIME, Equipment VARCHAR(100))

INSERT INTO #Test
SELECT '1','A','2011-03-03','TV' UNION ALL
SELECT '1','A','2011-03-03','RADIO' UNION ALL
SELECT '1','A','2011-03-03','PC' UNION ALL
SELECT '2','B','2011-03-03','RADIO' UNION ALL
SELECT '2','B','2011-03-03','TV' UNION ALL
SELECT '2','B','2011-03-03','PC' UNION ALL
SELECT '3','C','2011-03-03','CAR' UNION ALL
SELECT '4','D','2011-03-03','TV' UNION ALL
SELECT '1','A','2011-03-04','TV' UNION ALL
SELECT '1','A','2011-03-04','BIKE'

DECLARE @tbl2 AS TABLE (HomeNo_Name VARCHAR(100), DATE DATETIME, Equipment VARCHAR(100))

INSERT INTO @tbl2 
SELECT CONVERT(VARCHAR(10), HomeNo) + Name AS HomeNo_Name, Date, Equipment FROM #Test

;WITH C AS
(
SELECT * FROM @tbl2
) 

SELECT DISTINCT HomeNo_Name,Date, 
STUFF((SELECT '+' + Equipment FROM @tbl2 WHERE HomeNo_Name = C.HomeNo_Name AND DATE = C.DATE
FOR XML PATH('')),1,1,'') AS Name
FROM C

Vaibhav T

If I cant go back, I want to go fast...

newbie007
Starting Member

37 Posts

Posted - 2011-04-13 : 05:16:54

DECLARE @tbl2 AS TABLE (HomeNo_Name VARCHAR(100), DATE DATETIME, Equipment VARCHAR(100))

getting the following error for the above command

"Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'TABLE'."

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-04-13 : 05:23:40

quote:
Originally posted by newbie007

DECLARE @tbl2 AS TABLE (HomeNo_Name VARCHAR(100), DATE DATETIME, Equipment VARCHAR(100))

getting the following error for the above command

"Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'TABLE'."

What version of SQL Server are you using ? 2000 ?

KH
[spoiler]Time is always against us[/spoiler]

newbie007
Starting Member

37 Posts

Posted - 2011-04-13 : 05:28:22

quote:
Originally posted by khtan

quote:
Originally posted by newbie007

DECLARE @tbl2 AS TABLE (HomeNo_Name VARCHAR(100), DATE DATETIME, Equipment VARCHAR(100))

getting the following error for the above command

"Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'TABLE'."

What version of SQL Server are you using ? 2000 ?

KH
[spoiler]Time is always against us[/spoiler]

2000

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2011-04-13 : 05:29:51

which version of MS SQL server you are using ? This code is fine working on MS SQL 2005 and 2008

Raghu' S

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-04-13 : 05:30:39

for SQL 2000, remove the AS word as shown in my earlier post

KH
[spoiler]Time is always against us[/spoiler]

newbie007
Starting Member

37 Posts

Posted - 2011-04-13 : 06:22:58

For the foll command
INSERT INTO @tbl2
SELECT CONVERT(VARCHAR(10), HomeNo) + Name AS HomeNo_Name, Date, Equipment FROM #Test

getting the msg as
Server: Msg 137, Level 15, State 2, Line 2
Must declare the variable '@tbl2'.

Also is "WITH C AS
(SELECT * FROM @tbl2) " a separate command ?

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2011-04-13 : 06:31:59

You will have to execute complete code in one shot.

Vaibhav T

If I cant go back, I want to go fast...

newbie007
Starting Member

37 Posts

Posted - 2011-04-13 : 06:43:16

quote:
Originally posted by vaibhavktiwari83

You will have to execute complete code in one shot.

Vaibhav T

If I cant go back, I want to go fast...

I am running the below query :
DECLARE @tbl2 TABLE (HomeNo_Name VARCHAR(100), DATE DATETIME, Equipment VARCHAR(100))
INSERT INTO @tbl2
SELECT CONVERT(VARCHAR(10), HomeNo) + Name AS HomeNo_Name, Date, Equipment FROM #Test
;WITH C AS(SELECT * FROM @tbl2)

msg says : Server: Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'WITH'.

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2011-04-13 : 07:06:01

Add one more statement "SELECT * FROM C"
See the red part

quote:
Originally posted by newbie007

quote:
Originally posted by vaibhavktiwari83

You will have to execute complete code in one shot.

Vaibhav T

If I cant go back, I want to go fast...

I am running the below query :
DECLARE @tbl2 TABLE (HomeNo_Name VARCHAR(100), DATE DATETIME, Equipment VARCHAR(100))
INSERT INTO @tbl2
SELECT CONVERT(VARCHAR(10), HomeNo) + Name AS HomeNo_Name, Date, Equipment FROM #Test
;WITH C AS(SELECT * FROM @tbl2)
SELECT * FROM C

msg says : Server: Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'WITH'.

Vaibhav T

If I cant go back, I want to go fast...

newbie007
Starting Member

37 Posts

Posted - 2011-04-13 : 07:18:21

still getting the same error

Server: Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'WITH'.

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2011-04-13 : 07:25:14

Try this complete code -


CREATE TABLE #Test ( HomeNo INT, Name VARCHAR(100), Date DATETIME, Equipment VARCHAR(100))

INSERT INTO #Test
SELECT '1','A','2011-03-03','TV' UNION ALL
SELECT '1','A','2011-03-03','RADIO' UNION ALL
SELECT '1','A','2011-03-03','PC' UNION ALL
SELECT '2','B','2011-03-03','RADIO' UNION ALL
SELECT '2','B','2011-03-03','TV' UNION ALL
SELECT '2','B','2011-03-03','PC' UNION ALL
SELECT '3','C','2011-03-03','CAR' UNION ALL
SELECT '4','D','2011-03-03','TV' UNION ALL
SELECT '1','A','2011-03-04','TV' UNION ALL
SELECT '1','A','2011-03-04','BIKE'

DECLARE @tbl2 AS TABLE (HomeNo_Name VARCHAR(100), DATE DATETIME, Equipment VARCHAR(100))

INSERT INTO @tbl2 
SELECT CONVERT(VARCHAR(10), HomeNo) + Name AS HomeNo_Name, Date, Equipment FROM #Test

SELECT DISTINCT HomeNo_Name,Date, 
STUFF((SELECT '+' + Equipment FROM @tbl2 WHERE HomeNo_Name = C.HomeNo_Name AND DATE = C.DATE
FOR XML PATH('')),1,1,'') AS Name
FROM 
(
SELECT * FROM @tbl2
) C

Vaibhav T

If I cant go back, I want to go fast...

newbie007
Starting Member

37 Posts

Posted - 2011-04-13 : 07:29:14

Server: Msg 170, Level 15, State 1, Line 22
Line 22: Incorrect syntax near 'XML'.
Server: Msg 170, Level 15, State 1, Line 23
Line 23: Incorrect syntax near 'C'.

newbie007
Starting Member

37 Posts

Posted - 2011-04-14 : 03:56:13

Is it not possible to get the mentioned output ??

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2011-04-14 : 04:18:00

I think FOR XML PATH was not introduced in SQL 2000

Vaibhav T

If I cant go back, I want to go fast...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-04-14 : 04:56:14

for SQL 2000, use the method describe here
http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/rowset-string-concatenation-which-method-is-best.aspx