combin 2 cols has diff data in 1 col can u ? PIVOT

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)

combin 2 cols has diff data in 1 col can u ? PIVOT

Author

Topic

zaidan
Starting Member

8 Posts

Posted - 2008-07-28 : 23:31:54

Hi every one :

-My database has ( i just mentioned the table which are related to the problem ) :

entry table :
main_item & sub_item & cost & date is the entry data for this table

items table :
main_item FK & sub_item

main table :
main_item PK

i am able to reutrn reslut like this ( the total cost only for main_item per for each month ) :

item jun feb may
car 5000 5500 5250
Home 6000 4000 4000

using this code :

ALTER PROCEDURE [dbo].[test]

as
begin

with tempEntry
as (
select main_item ,cost , month ( daydate ) as Emonth
from [entry] where year(daydate) = 2008
)

SELECT *
FROm tempEntry PIVOT

(

sum(cost) FOR Emonth IN ([1],[7],[11] )) AS o

where as :
car = is main_item which contain sub_items ( oil , petrol , maintence , .... )
home = is main_item which contain sub_items ( electricity , water,reapring ,.. )

(((((( but the result i want is like this see below )))))) :

now suppose that i want to return the total for each main_item as well as ( sub_item (which is a part of main_item )) in one coulmn like this :

total cost for each month :

item jun feb may
car 1000 500 250
electricity 1000 2000 3000
oil 50 25 15

Thank you very reading my problem ....

hope to hear form you soon

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-29 : 00:26:11

[code];WITH tempEntry
AS
(
SELECT main_item, sub_item, cost, MONTH ( daydate ) AS Emonth
FROM [entry]
WHERE YEAR(daydate) = 2008
)
SELECT *
FROM
(
SELECT sub_item, cost, Emonth
FROM tempEntry
) s
PIVOT
(
SUM(cost) FOR Emonth IN ([1],[7],[11] )
) AS o

UNION ALL

SELECT *
FROM
(
SELECT main_item, cost, Emonth
FROM tempEntry
) s
PIVOT
(
SUM(cost) FOR Emonth IN ([1],[7],[11] )
) AS o
[/code]

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

zaidan
Starting Member

8 Posts

Posted - 2008-07-29 : 04:26:02

Thanks you khatan i tried using Union before but sql server keep showing this Error :

Msg 451, Level 16, State 1, Line 1
Cannot resolve collation conflict for column 1 in SELECT statement.

even with Your code still this message is poping up ...

thank you again Mr.khatan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-29 : 04:29:21

quote:
Originally posted by zaidan

Thanks you khatan i tried using Union before but sql server keep showing this Error :

Msg 451, Level 16, State 1, Line 1
Cannot resolve collation conflict for column 1 in SELECT statement.

even with Your code still this message is poping up ...

thank you again Mr.khatan

this is because the first column in query is having a different collation. try changing the collation by specifying explicitly collation using COLLATE clause.

zaidan
Starting Member

8 Posts

Posted - 2008-07-29 : 04:30:39

One more thing ....
if i replace union all with union from your code i keep getting this error :

Msg 468, Level 16, State 9, Line 1
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1256_CS_AS" and "SQL_Latin1_General_CP1256_CI_AS" in the UNION operation.

i think it is langugage issuse it seems to be ... but why ?

zaidan
Starting Member

8 Posts

Posted - 2008-07-29 : 04:34:29

different Collection what does it mean ?
datatype or what ...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-29 : 04:40:45

quote:
Originally posted by zaidan

different Collection what does it mean ?
datatype or what ...

COLLATION i meant. see this

http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1186718,00.html

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-29 : 04:43:37

Also refer this for use of COLLATE

http://www.elijournals.com/premier/showArticle.asp?aid=23701

zaidan
Starting Member

8 Posts

Posted - 2008-07-29 : 04:47:10

Thanks for porivding the link ..
But still can't understand ...

Thanks Visakh16 if there other resources Related to this matter please post them

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-29 : 08:35:29

Can you use SSMS to generate the Create table statement for table [entry] and post it here.

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

zaidan
Starting Member

8 Posts

Posted - 2008-07-29 : 12:51:00

Here it is Khtan :

USE [Myexpenses]
GO
/****** Object: Table [dbo].[entry] Script Date: 07/29/2008 19:50:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[entry](
[id] [int] IDENTITY(1,1) NOT NULL,
[main_name] [nvarchar](50) COLLATE SQL_Latin1_General_CP1256_CS_AS NOT NULL,
[sub_name] [nvarchar](50) COLLATE SQL_Latin1_General_CP1256_CI_AS NOT NULL,
[cost] [money] NOT NULL CONSTRAINT [DF_SubItem_T_cost] DEFAULT ((0)),
[IsItCredit] [bit] NOT NULL CONSTRAINT [DF_DaleyItem_T_IsItCredit] DEFAULT ((0)),
[daydate] [smalldatetime] NOT NULL CONSTRAINT [DF_entry_daydate] DEFAULT (getdate()),
[Remark] [nvarchar](300) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_entry_Remark] DEFAULT (N'No Remark'),
CONSTRAINT [PK_entry] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[entry] WITH CHECK ADD CONSTRAINT [FK_entry_MainItem_T] FOREIGN KEY([main_name])
REFERENCES [dbo].[MainItem_T] ([main_name])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[entry] CHECK CONSTRAINT [FK_entry_MainItem_T]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-29 : 12:55:20

Try like this

;WITH tempEntry
AS 
(
	SELECT 	main_item, sub_item, cost, MONTH ( daydate ) AS Emonth
	FROM 	[entry]
	WHERE 	YEAR(daydate) = 2008
)
SELECT	*
FROM	
(
	SELECT 	sub_item COLLATE SQL_Latin1_General_CP1256_CS_AS, cost, Emonth
	FROM 	tempEntry 
) s
PIVOT
(
	SUM(cost) FOR Emonth IN ([1],[7],[11] )
) AS o 

UNION ALL

SELECT	*
FROM	
(
	SELECT 	main_item, cost, Emonth
	FROM 	tempEntry 
) s
PIVOT
(
	SUM(cost) FOR Emonth IN ([1],[7],[11] )
) AS o

zaidan
Starting Member

8 Posts

Posted - 2008-07-29 : 13:40:29

Thank you very much brother for helping i trouble you a lot sorry for that :

but how about getting error like this from the code you provided in the last post :

Msg 8155, Level 16, State 2, Line 1
No column was specified for column 1 of 's'.

zaidan
Starting Member

8 Posts

Posted - 2008-07-31 : 22:19:49

where are you dear ..
i think no chance of solving this problem ....

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-01 : 06:44:44

[code];WITH tempEntry
AS
(
SELECT main_item, sub_item, cost, MONTH ( daydate ) AS Emonth
FROM [entry]
WHERE YEAR(daydate) = 2008
)
SELECT *
FROM
(
SELECT sub_item = sub_item COLLATE SQL_Latin1_General_CP1256_CS_AS, cost, Emonth
FROM tempEntry
) s
PIVOT
(
SUM(cost) FOR Emonth IN ([1],[7],[11] )
) AS o

UNION ALL

SELECT *
FROM
(
SELECT main_item, cost, Emonth
FROM tempEntry
) s
PIVOT
(
SUM(cost) FOR Emonth IN ([1],[7],[11] )
) AS o[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources