consider the following table:
name , TaskDate
john , 01/01/2006
john , 01/03/2005
steve , 01/05/2006
i want to build a select statement that gives result like the following:
name , JanuaryTotal , FebruaryTotal
john , 150 , 110
steve , 170 , 50
so the result will be total tasks in specific month, is it doable in one select or should i turn around using hash tables ???
I have created a crosstab query using the Pivot statement that returns the expected results. The results look similar to the sample below:
ItemKey Description Aflatoxin Coliform Bacteria E_Coli Fumonisin Melamine Moisture Mold Salmonella Vomitoxin (DON) Yeast
1000 Item1000 1 0 0 1 0 1 0 1 1 0
1024 Item1024 1 0 0 1 0 1 0 1 1 0
135 Item135 1 0 0 1 0 1 0 1 1 0
107 Item107 0 0 0 0 0 1 0 1 1 0
106 Item106 1 0 0 1 0 1 0 1 1 0
I'm using this statement to create the result set:
SELECT ItemKey, Description, Aflatoxin, [Coliform Bacteria], [E_Coli],[Fumonisin],
Melamine,Moisture, Mold, Salmonella, [Vomitoxin (DON)], Yeast
FROM
(SELECT tblInventory.ItemKey, tblInventory.Description,
jctProductClassificationRequiredTest.ProductTestClassID, tlbTestType.TestDescription
[Code] .....
Instead of doing a Count for the Pivot (the count will always be either 0 or 1 due to the design of the table being used), I would like to return an "X" for those records with a count of 1, and return a blank (otherwise null) for those records with a count of 0. So, the result set would look like:
ItemKey Description Aflatoxin Coliform Bacteria E_Coli Fumonisin Melamine Moisture Mold Salmonella Vomitoxin (DON) Yeast
1000 Item1000 X X X X X
1024 Item1024 X X X X X
135 Item135 X X X X X
107 Item107 X X X
106 Item106 X X X X X
I tried using a Case statement within the PIVOT portion, but I either did it incorrectly or it's not possible to do use a Case within the Pivot. Can I easily accomplish this?
Have a table with a structure like:
FIELD_A
FIELD_B
FIELD_C
FIELD_D
AMOUNT
DATA_A1
DATA_B1
DATA_C2
DATA_D2
37540
[Code] ....
In such a case, devising a dynamic cross-tab query, to cover all the items, and result like a pivot like the following to represent the data in a multi-header pivot like following:
DATA_C1
DATA_C1
DATA_C1
DATA_C2
DATA_C2
DATA_C2
[Code] ....
Hello everyone,
I have a tabel which looks like the follows:
CODE Type Number
1 account 20
1 empl 3
2 account 15
2 empl 6
3 account 32
3 empl 7
I need to show the results like
CODE Account Empl
1 20 3
2 15 6
3 32 7
I've tried different attempts but so far I always get 2 rows for each code,where one column shows a NULL value.
How do I have to do this ?
Is there a way to write Crosstab query in SQL 7.0.
I have a Table which has partner, usernames and the city they are from, apart from other fields. I have another table which tracks the user activity on a day by number of visits to the web site. What i want is like this :-
Date Total Visits Atlanta Connecticut .... .... ....
03/01/2001 5025 567 324 .... .... ....
03/02/2001 6789 423 146 .... .... ....
... ... ... ... .... .... ....
I.E. I wan't the city names to appear as columns. The main catch here is that this data is for a given partner. So the city names could be different for different users from different partners. i.e. city names have to be generated on the fly by looking at the partner table and then by getting all its users and seeing what all are the cities involved.
Any help would be appreciated.
Thanks
Sumit.
Is there a SQL version of a Crosstab Query??
View 14 Replies View RelatedCrosstab concepts
I m using Sql server 2000 version
I want to create a simple crosstab… as like the follows
My table is as like
Date warehouse (WH) Delivery No
7/7/2006 10 10-1
7/7/2006 20 20-1
7/7/2006 20 20-2
7/7/2006 30 30-1
8/7/2006 10 10-2
8/7/2006 10 10-3
11/7/2006 30 30-2
11/7/2006 20 20-3
I want to make a report using crosstab in iReport…..
I want report will be as like
Date WH10 WH20 WH30 total delivery
7/7/2006 1 2 1 4
8/7/2006 2 0 0 2
11/7/2006 0 1 1 2
my questions are:
i create a crosstab procedure and it is running but
01. I want to know the query.
02. How I count/sum the wh in the crosstab? (cause the WH is arrange as row in my database table )
I want step by step instructions
I have not clear concepts about crosstab, but I know crosstab will give the solution of this problem
Please help……
Thanks
shohan
Crosstab concepts
I m using Sql server 2000 version
I want to create a simple crosstab… as like the follows
My table is as like
Date warehouse (WH) Delivery No
7/7/2006 10 10-1
7/7/2006 20 20-1
7/7/2006 20 20-2
7/7/2006 30 30-1
8/7/2006 10 10-2
8/7/2006 10 10-3
11/7/2006 30 30-2
11/7/2006 20 20-3
I want to make a report using crosstab in iReport…..
I want report will be as like
Date WH10 WH20 WH30 total delivery
7/7/2006 1 2 1 4
8/7/2006 2 0 0 2
11/7/2006 0 1 1 2
my questions are:
i create a crosstab procedure and it is running but
01. I want to know the query.
02. How I count/sum the wh in the crosstab? (cause the WH is arrange as row in my database table )
I want step by step instructions
I have not clear concepts about crosstab, but I know crosstab will give the solution of this problem
Please help……
Thanks
shohan
Crosstab concepts
I m using Sql server 2000 version
I want to create a simple crosstab… as like the follows
My table is as like
Date warehouse (WH) Delivery No
7/7/2006 10 10-1
7/7/2006 20 20-1
7/7/2006 20 20-2
7/7/2006 30 30-1
8/7/2006 10 10-2
8/7/2006 10 10-3
11/7/2006 30 30-2
11/7/2006 20 20-3
I want to make a report using crosstab in iReport…..
I want report will be as like
Date WH10 WH20 WH30 total delivery
7/7/2006 1 2 1 4
8/7/2006 2 0 0 2
11/7/2006 0 1 1 2
my questions are:
i create a crosstab procedure and it is running but
01. I want to know the query.
02. How I count/sum the wh in the crosstab? (cause the WH is arrange as row in my database table )
I want step by step instructions
I have not clear concepts about crosstab, but I know crosstab will give the solution of this problem
Please help……
Thanks
shohan
Crosstab concepts
I m using Sql server 2000 version
I want to create a simple crosstab… as like the follows
My table is as like
Date warehouse (WH) Delivery No
7/7/2006 10 10-1
7/7/2006 20 20-1
7/7/2006 20 20-2
7/7/2006 30 30-1
8/7/2006 10 10-2
8/7/2006 10 10-3
11/7/2006 30 30-2
11/7/2006 20 20-3
I want to make a report using crosstab in iReport…..
I want report will be as like
Date WH10 WH20 WH30 total delivery
7/7/2006 1 2 1 4
8/7/2006 2 0 0 2
11/7/2006 0 1 1 2
my questions are:
i create a crosstab procedure and it is running but
01. I want to know the query.
02. How I count/sum the wh in the crosstab? (cause the WH is arrange as row in my database table )
I want step by step instructions
I have not clear concepts about crosstab, but I know crosstab will give the solution of this problem
Please help……
Thanks
shohan
Crosstab concepts
I m using Sql server 2000 version
I want to create a simple crosstab… as like the follows
My table is as like
Date warehouse (WH) Delivery No
7/7/2006 10 10-1
7/7/2006 20 20-1
7/7/2006 20 20-2
7/7/2006 30 30-1
8/7/2006 10 10-2
8/7/2006 10 10-3
11/7/2006 30 30-2
11/7/2006 20 20-3
I want to make a report using crosstab in iReport…..
I want report will be as like
Date WH10 WH20 WH30 total delivery
7/7/2006 1 2 1 4
8/7/2006 2 0 0 2
11/7/2006 0 1 1 2
my questions are:
i create a crosstab procedure and it is running but
01. I want to know the query.
02. How I count/sum the wh in the crosstab? (cause the WH is arrange as row in my database table )
I want step by step instructions
I have not clear concepts about crosstab, but I know crosstab will give the solution of this problem
Please help……
Thanks
shohan
Hi,
Currently working on a Attendance System project .
Iam storing the data in table against the employee code, date and status (basically a rowwise data).
Would require a query which can generate a cross-tab display with Employee Codes (on X axis), Dates (on Y-Axis) and with the Attendance Status.
The sample data for the same is as follows:
EmpCode Att_Date Att_Status
------- -------- ----------
001 01/01/2007 P
001 01/02/2007 A
.. ... ..
001 01/31/2007 P
002 01/01/2007 P
. . .
. . .
. . .
Would require the output as..
EmpCode 01/01/2007 01/02/2007 ...... 01/31/2007
------- ---------- ---------- ----------
001 P A P
002 A P P
. . . .
. . . .
. . . .
Thanking you in anticipation.
Jabez.
Can someone show me how to write a query to convert this:Year, Account, Qtr1, Qtr2, Qtr3, Qtr42004, 12345, 100, 200, 300, 4002005, 23456, 200, 300, 400, 500to this:Year, Account, Quarters, Amount2004, 12345, Qtr1, 1002004, 12345, Qtr2, 2002004, 12345, Qtr3, 3002004, 12345, Qtr4, 4002005, 23456, Qtr1, 2002005, 23456, Qtr2, 3002005, 23456, Qtr3, 4002005, 23456, Qtr4, 500So far I've only found examples which convert values to columns but notfor columns to values. TIA... AL
View 2 Replies View RelatedI have two tables Bill and Location.Bill(location_id int,prod_period datetime,consumption float,demand float)Location(location_id int,location_name varchar(45))I want to create a stored procedure that takes a parameter of @year. Ibasically want the procedure to return results that show locationswhere consumption and/or demand IS NULL or 0 for each month throughouta given year. I would like my results to look something like this:location_id year Jan Feb Mar Apr May Jun Jul Aug Sept OctNov Dec1 2005 0 0 02 2005 0 0 0 03 2005 0 0If data does exist for consumption or demand, I would like to show it,however I am really interested in the locations that have no dataassociated with them.Any ideas of how I can accomplish this?
View 1 Replies View RelatedI cant figure out what is going wrong here. I created this query to be able to show values across periods , and put it inside of a table instead of a matrix, for easier totals and subtotals. but for some reason my subquery is not pullin up the right amounts for "Goal Amount". The fields i have labeled as Period_1 , period_2 and so on work just fine. But the goals are not coming up with the same number as in the Sub query, if i ran it by its self. I dont know if maybe its the join, or what. but for some reason the numbers are way high then they should be. am i doing something wrong. Please let me know if i need to give more information.
This is what the output looks like now:
0.0000 0.0000 0.0000
4516182.0000 5569957.8000 2559169.8000
0.0000 0.0000 0.0000
0.0000 0.0000 0.0000
0.0000 0.0000 0.0000
3915126.9500 3275922.5500 1598011.0000
4526861.2500 6880829.1000 3078265.6500
629010.4000 589697.2500 353818.3500
2218175.9000 1984683.7000 1517699.3000
0.0000 0.0000 0.0000
0.0000 0.0000 0.0000
0.0000 0.0000 0.0000
this is what it looks like in the subquery:
150539.4000 2008 1 80 12
150539.4000 2008 2 80 12
150539.4000 2008 3 80 12
39313.1500 2008 1 81 12
39313.1500 2008 2 81 12
39313.1500 2008 3 81 12
181074.4500 2008 1 82 12
181074.4500 2008 2 82 12
181074.4500 2008 3 82 12
116746.1000 2008 1 86 12
116746.1000 2008 2 86 12
116746.1000 2008 3 86 12
79900.5500 2008 1 87 12
79900.5500 2008 2 87 12
79900.5500 2008 3 87 12
the two numbers highlighted should have the same amounts.
Code Snippet
SELECT cusSales.Report_Level_Id AS Report_Level, cusSales.Customer_Sales_Summary_Year AS Year, Goals.Region_Key,
Qry_Report_Level_Brand.Report_Level_Description, RC_DWDB_INSTANCE_1.dbo.Qry_Sales_Group_Dimension.Region_Name,
SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 1 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_1,
SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 2 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_2,
SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 3 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_3,
SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 4 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_4,
SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 5 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_5,
SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 6 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_6,
SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 7 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_7,
SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 8 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_8,
SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 9 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_9,
SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 10 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_10,
SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 11 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_11,
SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 12 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_12,
SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 13 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_13,
SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 1 THEN Goal_Amount ELSE 0 END) AS GOAL_1,
SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 2 THEN Goal_Amount ELSE 0 END) AS GOAL_2,
SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 3 THEN Goal_Amount ELSE 0 END) AS GOAL_3,
SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 4 THEN Goal_Amount ELSE 0 END) AS GOAL_4,
SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 5 THEN Goal_Amount ELSE 0 END) AS GOAL_5,
SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 6 THEN Goal_Amount ELSE 0 END) AS GOAL_6,
SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 7 THEN Goal_Amount ELSE 0 END) AS GOAL_7,
SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 8 THEN Goal_Amount ELSE 0 END) AS GOAL_8,
SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 9 THEN Goal_Amount ELSE 0 END) AS GOAL_9,
SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 10 THEN Goal_Amount ELSE 0 END) AS GOAL_10,
SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 11 THEN Goal_Amount ELSE 0 END) AS GOAL_11,
SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 12 THEN Goal_Amount ELSE 0 END) AS GOAL_12,
SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 13 THEN Goal_Amount ELSE 0 END) AS GOAL_13
FROM RC_DWDB_INSTANCE_1.dbo.Tbl_Territory_In_Customer INNER JOIN
RC_DWDB_INSTANCE_1.dbo.Tbl_Customer_Sales_Summary_Fiscal AS cusSales INNER JOIN
Qry_Report_Level_Brand ON cusSales.Sub_Brand_Id = Qry_Report_Level_Brand.Sub_Brand_Id ON
RC_DWDB_INSTANCE_1.dbo.Tbl_Territory_In_Customer.Customer_Code = cusSales.Customer_Code INNER JOIN
RC_DWDB_INSTANCE_1.dbo.Qry_Sales_Group_Dimension ON
RC_DWDB_INSTANCE_1.dbo.Tbl_Territory_In_Customer.Territory_Code = RC_DWDB_INSTANCE_1.dbo.Qry_Sales_Group_Dimension.Territory_Code FULL
OUTER JOIN
(SELECT SUM(cusSales.Customer_Sales_Summary_Amount) AS Goal_Amount, cusSales.Customer_Sales_Summary_Year AS Year,
cusSales.Customer_Sales_Summary_Period AS Period, cusSales.Report_Level_Id,
RC_DWDB_INSTANCE_1.dbo.Tbl_Territory_In_Region.Region_Key
FROM RC_DWDB_INSTANCE_1.dbo.Tbl_Customer_Sales_Summary_Fiscal AS cusSales INNER JOIN
RC_DWDB_INSTANCE_1.dbo.Tbl_Territory_In_Customer AS Tbl_Territory_In_Customer_1 ON
cusSales.Customer_Code = Tbl_Territory_In_Customer_1.Customer_Code INNER JOIN
RC_DWDB_INSTANCE_1.dbo.Tbl_Territory_In_Region ON
Tbl_Territory_In_Customer_1.Territory_Code = RC_DWDB_INSTANCE_1.dbo.Tbl_Territory_In_Region.Territory_Code
WHERE (cusSales.Customer_Sales_Summary_Year = 2008) AND (cusSales.Consolidated_Sales_Tables_Id = 8) and region_key=12
GROUP BY cusSales.Customer_Sales_Summary_Year, cusSales.Customer_Sales_Summary_Period, cusSales.Report_Level_Id,
RC_DWDB_INSTANCE_1.dbo.Tbl_Territory_In_Region.Region_Key) AS Goals ON
RC_DWDB_INSTANCE_1.dbo.Qry_Sales_Group_Dimension.Region_Key = Goals.Region_Key AND
cusSales.Report_Level_Id = Goals.Report_Level_Id AND cusSales.Customer_Sales_Summary_Year = Goals.Year AND
cusSales.Customer_Sales_Summary_Period = Goals.Period
WHERE (cusSales.Customer_Sales_Summary_Year IN (2007, 2008)) AND (cusSales.Consolidated_Sales_Tables_Id = 6) AND
(NOT (cusSales.Bill_Customer_Code IN ('11825', '11990', '11971'))) AND (cusSales.Report_Level_Id IN (78, 80, 81, 82, 86, 87)) AND
(RC_DWDB_INSTANCE_1.dbo.Qry_Sales_Group_Dimension.Group_Name <> 'BREEDER') AND
(RC_DWDB_INSTANCE_1.dbo.Qry_Sales_Group_Dimension.Group_Name <> 'OTHER') AND
(RC_DWDB_INSTANCE_1.dbo.Qry_Sales_Group_Dimension.Group_Name <> 'Vet - Corporate') AND
(RC_DWDB_INSTANCE_1.dbo.Qry_Sales_Group_Dimension.Group_Name <> 'export') AND
(RC_DWDB_INSTANCE_1.dbo.Qry_Sales_Group_Dimension.Group_Name <> 'Vet - Other') AND
(RC_DWDB_INSTANCE_1.dbo.Qry_Sales_Group_Dimension.Region_Key = 12)
GROUP BY cusSales.Report_Level_Id, cusSales.Customer_Sales_Summary_Year, Goals.Region_Key, Qry_Report_Level_Brand.Report_Level_Description,
RC_DWDB_INSTANCE_1.dbo.Qry_Sales_Group_Dimension.Region_Name
I am trıed to do crosstab in sql server 2005 but I failed. I look all document on the net but they are all static means the column that are crosed are avaliable. but ın my query I do not know the count of the columns... Please help me...
This the my query...
Yağmur Duman Türk Masajı 11
Mehtap . Türk Masajı 10
Mehtap . Japon Masajı 3
YaÄŸmur Duman TENÄ°S KORTU1 SAAT 1
Mehtap . TENÄ°S KORTU 1 SAAT 4
Yağmur Duman Kortu Kirası 1 saat 1
Mehtap . Kortu Kirası 1 saat 2
Administrator . BALI MASAJI 1
YaÄŸmur Duman BALI MASAJI 10
Mehtap . BALI MASAJI 11
Mehtap . 6 EL MASAJI 1
Mehtap . COUPLE MASAJ 1
YaÄŸmur Duman PILATES SINGLE 1
YaÄŸmur Duman CÄ°LT BAKIMI 3
Mehtap . CÄ°LT BAKIMI 4
Mehtap . Çikolata Masajı 2
YaÄŸmur Duman BAL BAKIMI 1
Mehtap . BAL BAKIMI 4
YaÄŸmur Duman 4 EL MASAJI 1
Mehtap . 4 EL MASAJI 2
YaÄŸmur Duman YOSUN BAKIMI 1
Mehtap . MASA TENÄ°SÄ° 1
Mehtap . HAMAM 1
Mehtap . KESE & KÖPÜK 1
YaÄŸmur Duman TRIO 1
Mehtap . TRIO 1
Özge Öztürk TRIO 1
Özge Öztürk 4 EL MASAJI 16
Ozan Çicek ISVEC MASAJI 1
Özge Öztürk ISVEC MASAJI 3
Ozan Çicek 4 EL MASAJI 5
Ozan Çicek BAL BAKIMI 3
Özge Öztürk BAL BAKIMI 10
Özge Öztürk THAI MASAJI 1
Ozan Çicek Çikolata Masajı 24
Özge Öztürk Çikolata Masajı 12
Ozan Çicek SICAK TAŞ 5
Özge Öztürk SICAK TAŞ 14
Özge Öztürk CİLT BAKIMI 17
Ozan Çicek SHATSU MASAJ 1
Özge Öztürk PILATES SINGLE 16
Özge Öztürk SHATSU MASAJ 4
Ozan Çicek CİLT BAKIMI 3
Ozan Çicek PILATES SINGLE 2
Özge Öztürk COUPLE MASAJ 10
Ozan Çicek COUPLE MASAJ 6
Özge Öztürk 6 EL MASAJI 16
Ozan Çicek 6 EL MASAJI 18
Ozan Çicek BALI MASAJI 44
Özge Öztürk BALI MASAJI 59
Ozan Çicek Kortu Kirası 1 saat 5
Özge Öztürk Kortu Kirası 1 saat 5
Ozan Çicek TENİS KORTU 1 SAAT 12
Özge Öztürk TENİS KORTU 1 SAAT 5
Ozan Çicek Japon Masajı 41
Özge Öztürk Japon Masajı 26
Ozan Çicek Türk Masajı 38
Özge Öztürk Türk Masajı 17
Yeşim Yıldırım Türk Masajı 32
Yeşim Yıldırım Japon Masajı 35
Yeşim Yıldırım TENİS KORTU 1 SAAT 3
Yeşim Yıldırım Kortu Kirası 1 saat 4
Yeşim Yıldırım BALI MASAJI 101
Yeşim Yıldırım 6 EL MASAJI 20
Yeşim Yıldırım COUPLE MASAJ 11
Yeşim Yıldırım SHATSU MASAJ 4
Yeşim Yıldırım PILATES SINGLE 3
Yeşim Yıldırım CİLT BAKIMI 10
Yeşim Yıldırım THAI MASAJI 1
Yeşim Yıldırım Çikolata Masajı 18
Yeşim Yıldırım SICAK TAŞ 14
Yeşim Yıldırım BAL BAKIMI 4
Yeşim Yıldırım 4 EL MASAJI 9
Yeşim Yıldırım HAMAM 1
Yeşim Yıldırım KESE & KÖPÜK 1
Yeşim Yıldırım SAUNA TEK GİRİŞ 3
Yeşim Yıldırım YOSUN BAKIMI 1
Yeşim Yıldırım TRIO 2
Funda Güngörür TRIO 3
Funda Güngörür 4 EL MASAJI 5
Funda Güngörür BAL BAKIMI 8
Funda Güngörür THAI MASAJI 4
Funda Güngörür Çikolata Masajı 10
Funda Güngörür SICAK TAŞ 14
Funda Güngörür SHATSU MASAJ 5
Funda Güngörür CİLT BAKIMI 18
Funda Güngörür PILATES SINGLE 9
Funda Güngörür COUPLE MASAJ 11
Funda Güngörür 6 EL MASAJI 15
Funda Güngörür BALI MASAJI 60
Funda Güngörür Kortu Kirası 1 saat 4
Funda Güngörür TENİS KORTU 1 SAAT 6
Funda Güngörür Japon Masajı 41
Funda Güngörür Türk Masajı 58
Ayşegül Duman Türk Masajı 22
Ayşegül Duman TENİS KORTU 1 SAAT 1
Ayşegül Duman Japon Masajı 17
Ayşegül Duman Kortu Kirası 1 saat 1
Ayşegül Duman BALI MASAJI 53
Ayşegül Duman 6 EL MASAJI 2
Ayşegül Duman COUPLE MASAJ 7
Ayşegül Duman SHATSU MASAJ 7
Ayşegül Duman PILATES SINGLE 9
Ayşegül Duman SICAK TAŞ 7
Ayşegül Duman CİLT BAKIMI 10
Ayşegül Duman Çikolata Masajı 5
Ayşegül Duman THAI MASAJI 1
Ayşegül Duman BAL BAKIMI 6
Ayşegül Duman ISVEC MASAJI 2
Ayşegül Duman TRIO 3
Ayşegül Duman KESE & KÖPÜK 1
Ayşegül Duman 4 EL MASAJI 15
Mert Ilgın HAMAM 2
Mert Ilgın YOSUN BAKIMI 2
Mert Ilgın SAUNA TEK GİRİŞ 1
Mert Ilgın MASA TENİSİ 1
Mert Ilgın 4 EL MASAJI 11
Mert Ilgın KESE & KÖPÜK 1
Mert Ilgın TRIO 2
Mert Ilgın ISVEC MASAJI 1
Mert Ilgın BAL BAKIMI 1
Mert Ilgın Çikolata Masajı 6
Mert Ilgın THAI MASAJI 1
Mert Ilgın SICAK TAŞ 11
Mert Ilgın CİLT BAKIMI 7
Mert Ilgın SHATSU MASAJ 3
Mert Ilgın PILATES SINGLE 6
Mert Ilgın 6 EL MASAJI 9
Mert Ilgın COUPLE MASAJ 11
Mert Ilgın BALI MASAJI 28
Mert Ilgın Kortu Kirası 1 saat 2
Mert Ilgın TENİS KORTU 1 SAAT 5
Mert Ilgın Japon Masajı 6
Mert Ilgın Türk Masajı 11
Aslı Öztürk Türk Masajı 14
Aslı Öztürk Japon Masajı 2
Aslı Öztürk TENİS KORTU 1 SAAT 1
Aslı Öztürk BALI MASAJI 31
Aslı Öztürk 6 EL MASAJI 2
Aslı Öztürk COUPLE MASAJ 1
Aslı Öztürk PILATES SINGLE 10
Aslı Öztürk CİLT BAKIMI 5
Aslı Öztürk SICAK TAŞ 4
Aslı Öztürk Çikolata Masajı 1
Aslı Öztürk BAL BAKIMI 2
Aslı Öztürk ISVEC MASAJI 1
Aslı Öztürk TRIO 3
Aslı Öztürk 4 EL MASAJI 2
Aslı Öztürk KESE & KÖPÜK 2
Ahmet Yılmaz TRIO 2
Ahmet Yılmaz Çikolata Masajı 2
Ahmet Yılmaz CİLT BAKIMI 5
Ahmet Yılmaz SICAK TAŞ 4
Ahmet Yılmaz PILATES SINGLE 20
Ahmet Yılmaz SHATSU MASAJ 1
Ahmet Yılmaz COUPLE MASAJ 2
Ahmet Yılmaz BALI MASAJI 22
Ahmet Yılmaz Japon Masajı 2
Ahmet Yılmaz Kortu Kirası 1 saat 1
Ahmet Yılmaz Türk Masajı 10
Ceren Yıldızdoğan Türk Masajı 4
Ceren Yıldızdoğan Japon Masajı 3
Ceren Yıldızdoğan BALI MASAJI 26
Ceren Yıldızdoğan 6 EL MASAJI 4
Ceren Yıldızdoğan SHATSU MASAJ 5
Ceren Yıldızdoğan PILATES SINGLE 12
Ceren Yıldızdoğan SICAK TAŞ 11
Ceren Yıldızdoğan CİLT BAKIMI 3
Ceren Yıldızdoğan Çikolata Masajı 3
Ceren Yıldızdoğan THAI MASAJI 1
this the query result but I want to do this
Türk Masajı TENİS KORTU1 SAAT Kortu Kirası 1 saat ............
YaÄŸmur Duman 11 1 1 ......
can you any idea about my problem
Hey,
i have a table which has the foll data:
employeecode Amount AmountDescription
1 100 x
2 200 y
3 150 x
4 300 z
now i need to fetch this data such that i can display the output as :
empcode x y z
1 100
2 200
3 150
4 300
any suggestions?????????
platform: SQL Server 2000
thanx!
month wise production
Format that I want
item codenameJulyAugSepOctNovDecjan
1002pvc resin 3020115060140
3501SWR pipe566045801002020
I create crosstab procedure as follows
create procedure up_CrossTab (@SelectStatement varchar(1000),
@PivotColumn varchar(100),
@Summary varchar(100),
@GroupbyField varchar(100),
@OtherColumns varchar(100) = Null)
AS
/*
Inputs are any 1000 character or less valid SELECT sql statement,
the name of the column to pivot (transform to rows), the instructions to summarize the data, the field you want to group on, and other fields returned as output. 1
*/
set nocount on
set ansi_warnings off
declare @Values varchar(8000);
set @Values = '';
set @OtherColumns= isNull(', ' + @OtherColumns,'')
/*
An 8000 varchar variable called @values is created to hold the [potentially filtered] values in the pivot column. @Values is initiated to an empty string. Then, a temporary table is created to hold each unique value. After the table is created, its rows are loaded into the variable @values. It's usefullness completed, the temporary table is destroyed. 2
*/
create table #temp (Tempfield varchar(100))
insert into #temp
exec ('select distinct convert(varchar(100),' + @PivotColumn + ') as Tempfield FROM (' + @SelectStatement + ') A')
select @Values = @Values + ', ' +
replace(replace(@Summary,'(','(CASE WHEN ' + @PivotColumn + '=''' +
Tempfield + ''' THEN '),')[', ' END) as [' + Tempfield )
from #Temp
order by Tempfield
drop table #Temp
/*
Finally, a dynamic sql select statement is executed which takes the GroupByField, and OtherColumns, passed into the procedure, and each of the Values from the Pivot Column from the passed in SELECT statement . 3
*/
exec ( 'select ' + @GroupbyField + @OtherColumns + @Values +
' from (' + @SelectStatement + ') A GROUP BY ' + @GroupbyField)
set nocount off
set ansi_warnings on
GO
And then my sql query is as like
EXEC up_CrossTab 'SELECT ProdId, GrnDate,Quantity FROM inteacc..IcGrnD IcGrnD
INNER JOIN inteacc..IcProduct IcProduct ON (IcGrnD.ProdId=IcProduct.ProdId) ',
'Year(GrnDate)', 'sum(Quantity)[]', 'ProdId'
error occurring
ambiguous column name ‘ProdId’
But when I compile this query
EXEC up_CrossTab 'SELECT grnNo,GrnDate,Quantity FROM inteacc..IcGrnD IcGrnD
INNER JOIN inteacc..IcProduct IcProduct ON (IcGrnD.ProdId=IcProduct.ProdId) ',
'Month(GrnDate)', 'sum(Quantity)[]','GrnNo'
Output
GrnNo12249
1220NullNull20
2Null20Null10
3NullNull300Null
4NullNull10Null
I could not understand the error.
What will I do to get the format I want?
shohan
How can I produce a Matrix/Crosstab table using SQL Query Analyzer/SQL Server?
I have the following tables:
people(unique_identifier, employee_number, known_as_and_surname ... )
exitin(unique_identifier, parent_identifier, category, score ... )
Relationship: exitint.parent_identifier = people.unique_identifier which is a one-many relationship (one employee can have many records in exitint)
example data:
please see attachment (cant make a pretty table appear in the post...)
The zip file has an excel spreadsheet with what I have and want to produce as an example.
I want to make this in SQL (SQL Server 2000 I think)
I have been looking all over the internet for a solution but I just cant get one to work! :eek:
Thank you all in advance! <3
Hello --I think this is the term for what I want (something that could be generatedin ACCESS using a pivot table, or, maybe Yukon).We have data for sales by sales people in sales regions. More than oneperson sells in a region.We want to display data as follows:salesperson's names----------- ----------- ----------- ----------- ----------- -----------region 1region 2 row/column values are sales amounts for person inthat regionregion 3We will add a WHERE clause for the period of time covered.I don't want to have to change the query if a new salesperson or new regionis added.Can this be done in SQL Server 2000, Analysis Services, OLAP, anywhere?Can someone direct me to examples of how to do this?Thanks for any direction.Larry Mehl
View 2 Replies View RelatedHello,
I'm creating a website for a student organization at my university. Basically, I have an Excel file with a list of contacts that the members of our organization need to be able to search on our website.
The only field that I need our members to be able to search is "contact location." So for example, a member might want to know if our organization has any contacts in Spain, so the member would type "spain" into the "contact location search" field" and would get results.
I really don't known where to turn... I need the easiest thing that would be manageable for a novice. It doesn't need to be an intense search function; just something to make the website functional (so it obviously needs to be uploadable to a server; the server has PHP capability...no idea if that has any relevance). I've been searching around all day and have come across potentials like PHP, SQL, XML, etc. but have no idea which way to turn... I have Office and Adobe Studio programs at my disposal.
Thank you all-powerful and all-knowing web gods! I owe you my deepest gratitude!
Mikey
:beer:
I found in error log of my server is full of message like this
The query notification dialog on conversation handle '{9AD14600-28AD-DC11-9B36-000D5670268E}.' closed due to the following error: '<?xml version="1.0"?><Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8494</Code><Description>You do not have permission to access the service 'SqlQueryNotificationService-174a4df0-bac8-4a51-8564-28d750d7c11a'.</Description></Error>'.
any idea what could cause that?
thanks!
I can use crosstab query(intersection table) in sql server.
for example :
CREATE TABLE [Test] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[name] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[subject] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Source] [numeric](18, 0) NULL
) ON [PRIMARY]
GO
INSERT INTO [test] ([name],[subject],[Source]) values (N'mike,N'math,60)
INSERT INTO [test] ([name],[subject],[Source]) values (N'tom',N'English',70)
INSERT INTO [test] ([name],[subject],[Source]) values (N'mike',N'france',80)
INSERT INTO [test] ([name],[subject],[Source]) values (N'jane',N'English',75)
INSERT INTO [test] ([name],[subject],[Source]) values (N'mary',N'france',57)
INSERT INTO [test] ([name],[subject],[Source]) values (N'tom',N'math',80)
INSERT INTO [test] ([name],[subject],[Source]) values (N'mike',N'Englist',100)
Go
--------
declare @sql varchar(8000)
set @sql = 'select name,'
select @sql = @sql + 'sum(case subject when ''+subject+''
then source else 0 end) as ''+subject+'','
from (select distinct subject from test) as a
select @sql = left(@sql,len(@sql)-1) + ' from test group by name'
exec(@sql)
But I cannot use it in sql server mobile editon.
HOw do I? thank you very much!!!
I need some help in converting this crosstab SQL from an Access query to a View in SQL Server Express:TRANSFORM First(tblPhones.PhoneNumber) AS FirstOfPhoneNumber
SELECT tblPhones.ClientID
FROM tblPhones
GROUP BY tblPhones.ClientID
PIVOT tblPhones.PhoneType;
Hi, I have the following query that kinda does what i want
SELECT ABTANumber, TourOperator, ReportStatus,
COUNT(*) AS Counter
FROM (SELECT ABTANumber, TourOperator, r.ReportStatus FROM bookingdetails bd LEFT JOIN report r ON bd.Id = r.BookingDetailsId) a
GROUP BY ABTANumber, TourOperator, ReportStatus
This is what it displays
TourOp|ReportStatus|Count
JMC..... Fail.............. .10
JMC..... Pass..............621
JMC..... Warn.............5
SET..... Fail.............. .12
SET..... Pass..............621
SET..... Warn.............3
But what i want to display is this
TourOp|Pass|Fail|Warn
JMC......621 ..10....5
SET..... 621...12....3
I'm really stuck on this and would appreciate any help
thanks
Mark
hi,
I've table TAB_QUESTION:
ID_QUESTION..........VALUE_ID..........NUM_ANSWER. .....DESC_ANSWER
XB1.................1...................0......... .....YES
XB1.................2...................0......... .....NO
XB1.................3...................1......... .....GOOD
XB1.................4...................0......... .....SUFF
XB1.................5...................1......... .....NO_GOOD
XB1.................6...................0......... .....NR
XB1.................7...................0......... .....NN
YB1.................1...................1......... .....YES
YB1.................2...................2......... .....NO
YB1.................3...................3......... .....GOOD
YB1.................4...................0......... .....SUFF
YB1.................5...................3......... .....NO_GOOD
YB1.................6...................2......... .....NR
YB1.................7...................1......... .....NN
ZC1.................1...................0......... .....YES
ZC1.................2...................0......... .....NO
ZC1.................3...................0......... .....GOOD
ZC1.................4...................0......... .....SUFF
ZC1.................5...................0......... .....NO_GOOD
ZC1.................6...................0......... .....NR
ZC1.................7...................1......... .....NN
TC1.................1...................1......... .....YES
TC1.................2...................1......... .....NO
TC1.................3...................1......... .....GOOD
TC1.................4...................1......... .....SUFF
TC1.................5...................1......... .....NO_GOOD
TC1.................6...................0......... .....NR
TC1.................7...................0......... .....NN
.................................................. ........
.................................................. ........
I've always JUST 7 (seven) DESC_ANSWER (YES,NO,GOOD,SUFF,NO_GOOD,NR,NN)
Now I'd like to have ID_QUESTION like columns and DESC_ANSWER like rows.
like this:
DESC_ANSWER..........XB1........YB1............ZC1 ........TC1
YES.........................0..........1.......... ....0...........1
NO...........................0..........2......... .....0...........1
GOOD.......................1..........3........... ...0...........1
SUFF........................0..........0.......... ....0...........1
NO_GOOD..................1..........3............. .0...........1
NR...........................0..........2......... .....0...........0
NN...........................0..........1......... .....1...........0
How Can I write this query to get this output??
Thanks in advance!
SELECT
[R].[PaymentMonth],
[S].[RegionCode],
[S].[CmsStateShortName],
[P].[Attribute1] AS [FinalProduct],
[Membership] = SUM([R].[Membership])
FROM
[RptMMRSummary1] [R] INNER JOIN [RefCmsState1] [S]
ON [R].[CmsStateCode] = [S].[CmsStateCode]
INNER JOIN [RefPlanBenefitPackage1] [P]
ON [R].[PlanBenefitPackageID] = [P].[PlanBenefitPackageID]
WHERE
[R].[PaymentMonth] IN ('200712', '200711', '200612')
-- [P].[Attribute1] IN ('HMO', 'PPO', 'PFFS', 'SNP', 'EVCSNP')
GROUP BY
[R].[PaymentMonth],
[S].[RegionCode],
[S].[CmsStateShortName],
[P].[Attribute1]
-------------------------------------------------------------------
How do we use the pivot query for the above script. Layout as below
200801 Month / Year Selection
-------------------------------------------------------
StateShortname | Attribute | Attribute | Attribute | Total
----------------------------------------------------------
AL Values Values Values
OL Values Values Values
ZW Values Values Values
WEST Group By Region Code Total
BK Values Values Values
MN Values Values Values
EAST Group By Region Code Total
200712 Always Previous Month for the above selection Month /Year
-------------------------------------------------------
StateShortname | Attribute | Attribute | Attribute | Total
----------------------------------------------------------
AL Values Values Values
OL Values Values Values
ZW Values Values Values
WEST Group By Region Code Total
BK Values Values Values
MN Values Values Values
EAST Group By Region Code Total
200612 Always Previous year End Month for the above select Month / Year
-------------------------------------------------------
StateShortname | Attribute | Attribute | Attribute | Total
----------------------------------------------------------
AL Values Values Values
OL Values Values Values
ZW Values Values Values
WEST Group By Region Code Total
BK Values Values Values
MN Values Values Values
EAST Group By Region Code Total
Output :
[RegionCode],
[CmsStateShortName],
CurMonth_HMO, CurMonth_PPO, CurMonth_PFFS, CurMonth_SNP, CurMonth_EVCSNP,
PrevMonth_HMO, PrevMonth_PPO, PrevMonth_PFFS, PrevMonth_SNP, PrevMonth_EVCSNP,
PrevYrEndMonth_HMO, PrevYrEndMonth_PPO, PrevYrEndMonth_PFFS, PrevYrEndMonth_SNP, PrevYrEndMonth_EVCSNP
I€™ve a table with 2 columns with the following data
Col 1 Col2
---------------------------
Vista 2005
Distinguish 2005
ColonialVoice 2006
Vista 2006
Vista 2007
SuperiorCard 2007
Distinguish 2007
I would like to get the output in the following format using PIVOT function
2005 2006 2007
---------------------------------------------------------
Vista ColonialVoice Vista
Distinguish Vista SuperiorCard
Distinguish
Help me to write the query€¦
--
ash
I€™ve a table with 2 columns with the following data
Col 1 Col2
---------------------------
Vista 2005
Distinguish 2005
ColonialVoice 2006
Vista 2006
Vista 2007
SuperiorCard 2007
Distinguish 2007
I would like to get the output in the following format using PIVOT function
2005 2006 2007
---------------------------------------------------------
Vista ColonialVoice Vista
Distinguish Vista SuperiorCard
Distinguish
Help me to write the query€¦
--
ash
Hi,
I have a table EmpProject with following:
=========================
unitid projectid employees
----------- --------- -----------------------
1 CCT1 Ravi,Raja,Kanna
2 CCT1 John,Vijay,Nithya
1 CCT2 Senthil,Lee,Suresh
2 CCT2 Ram,Krish,Latha
2 CCT3 Raja,Vijay,Ram
2 CCT4 Sankar
TSql to create the above :
create table empproject (unitid int, projectid varchar(4), employees varchar(1000))
insert empproject select 1,'CCT1', 'Ravi,Raja,Kanna'
insert empproject select 2,'CCT1', 'John,Vijay,Nithya'
insert empproject select 1,'CCT2', 'Senthil,Lee,Suresh'
insert empproject select 2,'CCT2', 'Ram,Krish,Latha'
insert empproject select 2,'CCT3', 'Raja,Vijay,Ram'
insert empproject select 2,'CCT4', 'Sankar'
I would like to have it in the following format
========================================================
unitid CCT1 CCT2 CCT3 CCT4
----------------------------------------------------------------------------------------------------------------
1 Ravi,Raja,Kanna Senthil,Lee,Suresh
2 John,Vijay,Nithya Ram,Krish,Latha Raja,Vijay,Ram Sankar
Is there any way to query?
Thanks in Advance,
Nithyapriya
Hi!
I have a table Items(id, name, section, id_format, price) and a table Format(id, format,order)
What I want to do is display: Name, Format1, Format2, Format3.
How can I acheive that result??
THanks!!
-JTM
I have tbWarehouseStock, contain
WHCode Item Stock
------ ------ -----
WH001 Pencil 10
WH001 Pen 10
WH002 Pencil 5
WH003 ruler 100
How to make pivot query like this, I am thinking of dynamic SQL but dont know how to do this
Item WH001 WH002 WH003 WH004 WH005 ....
------ ----- ----- ----- ----- -----
Pencil 10 5 0
Pen 10 0 0
Ruler 0 0 100
thanks
(SQL Server 2005)
I have the following query (trying to execute in Managment Studio):
SELECT Rate, Lender, Pricing, Max(Pricing) AS Maximum, Min(Pricing) As Minimum, Avg(Pricing) As Median
FROM [10_Tier].[dbo].[Temp10Tier_1000]
PIVOT (Sum(Pricing) For Lender)
I get the error: Incorrect syntax near ')' Microsoft SQL Server Error 102
I've been looking all over and have found many examples but I cannot get any of them to work.
What I want to accomplish is to be able to create a pivot query and use it in the "Query Builder" when designing a report for the reporting services, so that I can have a table that mimicks a "Matrix" - so that I can have multiple value columns.