Tables And Rows Counts PIVOT
May 26, 2015Script for finding row counts of two specific tables in each database of SQL in PIVOT format . e.g
Database || TableName1| Tablename2|| RowsCounts
ADVERTISEMENT
Power Pivot :: Measure Count Rows - If Condition - Multiple Tables
Nov 7, 2014I want to count the rows in the Incident Table by using filters to limit the rows to be counted if they meet the below conditions. I know I need a logical test for each row of the incident table based on the apparatus table’s rows. But, I want to test for each row in the incident table, counting, but not returning a true or false in the overall measure.Something like look at each incident row, test for true or false and then count IF the statement is true. Then go to the next incident row and do the same. The aggregation would be the final count of “true” results.I tried this for MET objective:
=CALCULATE(COUNTROWS(incident),
apparatus[Incident Response Time] >-1 ||
apparatus[Incident Response Time] <320,
uv_901APP_TYPE[Description]="Engine",
uv_901INCIDENT[Top_Category]="Fire"
[code]....
SQL 2012 :: Extract All Tables Names And Their Row Counts From Linked Server Tables
Oct 7, 2015I am using the following select statement to get the row count from SQL linked server table.
SELECT Count(*) FROM OPENQUERY (CMSPROD, 'Select * From MHDLIB.MHSERV0P')
MHDLIB is the library name in IBM DB2 database. The above query gives me only the row count of table MHSERV0P. However, I need to get the names, rowcounts, and sizes of all tables that exist in MHDLIB librray. Is it possible at all?
Power Pivot :: Force Measure To Be Visible For All Rows In Pivot Table Even When There Is No Data?
Oct 13, 2015Can I force the following measure to be visible for all rows in a pivot table?
Sales Special Visibility:=IF(
HASONEVALUE(dimSalesCompanies[SalesCompany])
;IF(
VALUES(dimSalesCompanies[SalesCompany]) = "Sales"
;CALCULATE([Sales];ALL(dimSalesCompanies[SalesCompany]))
;[Sales]
)
;BLANK()
)
FYI, I also have other measures as well in the pivot table that I don't want to affect.
Power Pivot :: One Slicer To Control Two Pivot Tables That Have Different Source Data And Common Key
Jul 8, 2015I have two data tables:
1) Production data with column headers: Key, Facility, Line, Time, Output
2) Costs data with column headers: Key, Site, Cost Center, Time, Cost
The tables have a common key named obviously as Key. The data looks like this:
Key
Facility
Line
Time
Output
Alpha
I would like to have two pivot tables which I can filter with ONE slicer based on the column Key. The first pivot table shows row labels Facility, Line and column labels Time. Value field is Output. The second pivot table shows row labels Site, Cost Center, and column lables Time. Value field is Cost.How can I do this with Power Pivot? I tried by linking both tables above to a table with unique Keys in PowerPivot and then creating a PivotTable where I would have used the Key from the Keys table.
View That Conditionally Counts Rows With Reset
May 22, 2015I have a table sorted by vendor, then item, then Status date with a QC Pass date and Fail date.
I need a SQL 2008 view that counts how many consecutive times a Vendor/Item has passed QC.
When it fails, the count resets to zero, then begins incrementing again.
I need to know how to generate the last column (Count).
I have tried using a ROW_Number() OVER(Partition BY, Order By...) command in the view, but I cannot seem to make it work right.
VendorItemStatusDatePassDate Faildate Count
10056322010-05-092010-05-091
10056322012-12-152012-12-152
10056322013-05-252013-05-253
10056322014-11-172014-11-174
[Code] ....
DTS Package - Compare Row Counts Between Tables
Jun 14, 2005Hello,
I am working on my first DTS package in about 2 years. I am using MS SQL Server 2000.
I have 10 tables and will be importing those tables from 10 text files each night. The files will be imported to tables with temp_ as a prefix. What I need to do is then compare the row counts of the imported (temp_) tables to the ones that are there from the previous import (tables without the temp_ prefix). If the row count between each of the tables is greater than a 10% variance +/- then I need to abort the import and send an email to the client. If the variance is less than 10% for all 10 tables then I need to conitinue with the rest of the DTS which will drop all the tables from the previous import and then rename all the imported tables to remove the temp_ prefix so that they become the new "production" tables. I have it setup to import to the temp_ tables and that is working just fine. I have it setup to drop and rename the tables... what I need help with is how I would go about doing the row count comparison. Can in be done directly in DTS, do I need to do it in a stored procedure called by the DTS? If someone can just get me pointed in the right direction with this it would be great!
Thanks,
Jake
List Record Counts Of All Tables?
Jun 2, 2004Hi All,
Is there a fancy way to list all table names with record counts?
Using table: INFORMATION_SCHEMA.TABLES
Also, Is there a way to initialize/empty all data from all tables?
Thank you very much
Rename Tables With 0 Record Counts
Apr 1, 2008I want to write a proc that will loop through a database and if a table has 0 record counts then the table is renamed to 'ZZZ_tablename'. Should I use this to get the row counts? or what is the best way to do this?
DBCC UPDATEUSAGE(GCMC_DISCGRX) WITH COUNT_ROWS
select object_name(id) as table_name,rows from sysindexes
where indid<2 AND INDID = 0 and rows = 0
order by 1
then EXEC sp_rename @emptytables, 'ZZZ_' + @emptytables
SQL Server 2012 :: Row Counts Based On Distinct Values From Multiple Tables
Jan 15, 2015I am trying to create a query that outputs the following data from multiple tables. Here is an example of the raw data right now
Date | MachineNumber | TestName
---------------------------------------
1/1/2015 | 500 | Something
1/1/2015 | 500 | Something
1/1/2015 | 500 | Something
1/1/2015 | 500 | Something
1/1/2015 | 510 | NewTest
1/1/2015 | 510 | NewTest
1/1/2015 | 510 | NewTest
1/1/2015 | 620 | Test#1
Here is the desired counted output, I would like to pull distinct Date, MachineNumber, TestName and then count how many times they occur in the raw data form.I do need to perform a case on the date because right now its in a datetime format and I only need the date.
Date | MachineNumber | TestName | TestOccuranceCount
-----------------------------------------------------------------
1/1/2015 | 500 | Something | 4
1/1/2015 | 510 | NewTest | 3
1/1/2015 | 620 | Test#555 | 1
I am pulling three columns with the same names from 8 different tables. What I need to display the date, machine & test name and count how many times a test was run on a machine for that date. I have a feeling this can be handled by SSAS but haven't built an analysis cube yet because I am unfamiliar with how they work. I was wondering if this is possible in a simple query. I tried to set something up in a #Temp table. Problem is the query takes forever to run because I am dealing with 1.7 Million rows. Doing an insert into #temp select columnA, columnB, columnC from 8 different tables takes a bit.
How To Pivot Some Rows Into One Column
Mar 4, 2008
Hi,
I have issue about how to pivot and merge some rows into one column.
I have the table name : tableA with columns and records as below shown:
Cola Colb
------ ------
A A1
A A2
A A3
B B1
B B2
The request is to display the query like this :
Cola Colb
------- -----------------------------
A A1A2A3
B B1B2
I get stuck with my query below :
select Cola, ???
from tableA
group by Cola
Do you have idea how to achive this with TSQL?
Thanks.
Kasim
How To Concat/pivot Rows To Column?
Apr 6, 2004hello,
I'm wondering how it's possible to have a select statement resultant rows concatenated into one row and column.
For example:
select letter from alphabet_table
a
b
c
d
e
...
26 rows returned.
Other than a cursor, how would I write a query to return the following:
row1: abcdefghijkl...
thanks in advance!
Invert Rows And Columns (PIVOT)
Jul 10, 2006Hi everybody.
I have this table that contains how many items were sold (and their value) into two departments:
SellerID(PK)
Year(PK)
ItemsSoldDPT1
ItemsSoldDPT2
ValueSoldDPT1
ValueSoldDPT2
1
2002
10
20
300.00
400.00
1
2003
13
71
450.00
320.00
1
2004
8
4
350.00
640.00
1
2005
2
15
110.00
680.00
2
2001
3
1
130.00
100.00
2
2005
1
7
190.00
200.00
2
2006
6
9
170.00
500.00
...
...
...
...
...
...
I'm trying to write a query that puts the data present in the "Year" column as if they were in a row (column definitions)..making sums of pieces and values.. or.. to be more clear..
I want to obtain this:
SellerID
2001 Items
2001 Values
2002 Items
2002 Values
2003 Items
2003 Values
2004 Items
2004 Values
2005 Items
2005 Values
2006 Items
2006 Values
...
1
(NULL)
(NULL)
30
700.00
84
770.00
12
990.00
17
790.00
(NULL)
(NULL)
...
2
4
230.00
(NULL)
(NULL)
(NULL)
(NULL)
(NULL)
(NULL)
8
390.00
15
670.00
...
...
...
..
...
...
...
...
...
...
...
...
...
...
...
Any ideas? I think I should use the PIVOT keyword to write the sql but I can't figure how it works and how can I do that sums.
Please note: I don't know how many distinct values of "Year" exists and the min and max year can be specified by the user.
Thank you for your help.
PIVOT To Return Dynamic Rows Horizontally
Jan 13, 2008Hi,
I have a client with the following table structure:
ItemNumber, Name, Description
containing the following Data (example):
ItemNumber
Name
Value
6473764
SDRAM
4GB
6473764
Xeon
2300 Mhz
6473764
Video
256 MB
6473764
Bus
1300 Mhz
6473759
SDRAM
2GB
6473759
Xeon
2000 Mhz
6473759
Video
128 MB
6473759
Bus
1066 Mhz
I am trying to use PIVOT to convert this into Columns:
Item Number
SDRAM
Xeon
Video
Bus
6473764
4GB
2300 Mhz
256 MB
1300 Mhz
6473759
2 GB
2000 Mhz
128 MB
1066 Mhz
The problem is that I do not know the names of the columns before I run the query. I retrieve them based on a a list of Item Numbers:
SELECT DISTINCT Name FROM TableName WHERE ItemNumber IN (...)
What I was trying to do is the following, but I am not exactly sure what is wrong:
WITH ProductLines (ItemNumber, ItemName, ItemValue)
AS
(
SELECT ItemNumber, Name, Value FROM ProductItems WHERE ItemNumber IN (...)
)
SELECT * FROM ProductLines -- Don't know the column names
PIVOT
(
... -- Not sure what should come here
FOR ItemNumber IN (...)
)
Thanks a lot,
Aric Levin
Transact SQL :: Transpose (Pivot) Columns To Rows?
Aug 15, 2015I need to pivot my records in columns into rows. Here is sample data:
create table #columnstorows
(
Payer varchar (5),
ID varchar (5),
ClM varchar (2),
Paid1 float,
Paid2 float,
Paid3 float
)
[code]....
Desired result:
Payer1 Payer2
ID1 ID2
Paid1A Paid1B
Paid2A Paid2B
Paid3A Paid3B
U001 U002
001 002
76.58 19.53
153.48 96.43
53.48 200
Transact SQL :: Pivot Columns Into Rows With Difference
Sep 22, 2015Here is the table:
empid lastname firstname title titleofcourtesy ModifiedOROriginal
1 Davis1 Sara CEO Ms. Modified
1 Davis Sara CEO Ms. Original
We need an output like this:
empid
1
1
lastname
Davis1
Davis
firstname
Sara
Sara
title
CEO
CEO
titleofcourtesy
Ms.
Ms.
ModifiedOROriginal
Modified
Original
Attempting To Transpose/pivot Columns To Rows... I Think.
Jun 26, 2006I have something of a challenge for you folks (at least it's very challenging for me).
I have a table that has data that looks like this:
Date______OrderNum____WorkDay
2006-06-1__AA_________1
2006-06-1__AB_________1
2006-06-2__BA_________2
2006-06-2__BB_________2
2006-06-2__BC_________2
2006-06-5__CA_________3
2006-06-5__CB_________3
etc.
So, there are dates that orders happened and each date is marked as the 1, 2, 3, etc. business day of the month.
What I am trying to write is a SQL statement that will display results thus:
Day1______Day2______Day3_______etc.
AA________BA_______CA_________etc.
AB________BB_______CB_________etc.
Is this making any sense to anyone? Basically, I need to turn the WorkDay rows into columns and display all the info for each WorkDay under them.
I have a feeling this isn't hard, but I am fairly new to writing SQL so any advice would be pure gold to me. Thanks!
URGENT Plz ! Pivot Rows To Columns In SQL Server 2000
May 16, 2007Hi,
I have the following data in a table called "Products" :
Product Qty LHinging RHinging
-------- ----- -------- --------
Panel_1 1 R
Panel_1 1 L
Panel_1 1 R
Panel_1 1 R
Panel_2 1
Panel_2 1 L
I need to group this data like this :
Product Qty LHinging RHinging
-------- ----- -------- --------
Panel_1 3 L R
Panel_2 1
Panel_2 1 L
How can I do that ???
Thanks !
SQL Server 2012 :: How To Pivot Column To Rows Within A Group
Dec 18, 2014I need to convert the column to rows but within group. example
Group Name Value
p a 1
p b 2
p c 3
p d 4
q a 5
q b 6
q d 7
r a 8
r b 9
r c 10
r d 11
This need to be transposed to :
Group a b c d
p1234
q56NULL7
r891011
SQL Server 2012 :: Pivot Rows And Columns In The Same Query?
Mar 26, 2015I currently have data stored in a temporary table and I would like to transpose the data into a better format. I would like for the query to be dynamic since one of the tables currently has over 500 columns.
The attached file provides an example of the table structure along with sample data. Below the first set of data is the desired final format.
SQL Server 2008 :: How To Merge Two Rows Into One (conditions Or Pivot)
Jul 8, 2015Merge two rows into one (conditions or Pivot?)
I have Temptable showing as
Columns:EmpName, Code, Balance
Rows1: EmpA, X, 12
Rows2: EmpA, Y, 10
I want to insert the above temp table to another table with column names defined below like this
Empname, Vacation Hours, Sicks Hours
EmpA, 12, 10
Basically if it is X it is vacation hours and if it is Y it is sick Hours. Needs a simple logic to place the apprpriate hours(Balance) to its respective columns. I'm not sure how I can achieve this in using Pivot or Conditions.
SQL Server 2012 :: Transpose (Pivot) Columns To Rows?
Aug 15, 2015I need to pivot my records in columns into rows. Here is sample data:
create table #columnstorows
(
Payervarchar (5),
IDvarchar (5),
ClMvarchar (2),
Paid1float,
[Code] ....
Desired result:
Payer1Payer2ID1ID2Paid1APaid1BPaid2APaid2BPaid3APaid3B
U001 U002 00100276.58 19.53 153.4896.43 53.48 200
Power Pivot :: Update Specific Rows In Table
May 21, 2015I have a text file which I used as an initial load of data, containing a little over a million rows. I would like to produce a daily "update" file, which contains changes to existing rows, and creation of new rows. What is the best method of updating the existing table in power pivot?
View 2 Replies View RelatedPower Pivot :: How To Count Rows Where A Specific Value Exist
May 20, 2015This is primarily a question for DAX/Power BI. I'm trying to figure out how to count rows for a particular column where only a specific value exist. For instance, I'm trying to count how many times a certain field contains a true value or false value across multiple columns.
I found this formula here: [URL] ....
=CALCULATE(COUNTROWS('table'), ALLEXCEPT('table', 'table'[n_country]))
However, I'm not quite sure how to adapt it to count only rows where a value is equal to True, False, 17, "foobar", etc...
SQL Pivot Tables
Feb 29, 2008Hi,
I am still having trouble trying to find out how to use SQL for a pivot table. Can someone help me out, please?
Thanks
Wendy
Pivot Tables
Aug 10, 2006Hi,
I'm wanting to manipulate data between two tables in the following manner.
Table1
Column1 Derived Column ValueX ValueY
1 0 100 100
2 0 40 60
3 0 30 70
4 0 90 85
5 0 10 102
Table 2
IDColumn Qty1 Qty2 Qty3 Qty4 Qty5
a 10 20 30 40 50
I need to take the data in columns Qty1 - Qty5 in Table2 and transpose it into the Derived Column in Table 1.
Leaving the ValueX and ValueY columns as they are relative to column1.
I believe this can be done using a Pivot Table, but cannot find enough clear information from which to learn how to do it.
Any help will be welcomed.
John
SQL Server 2014 :: Pivot Table With Column Names To Rows?
Aug 1, 2015I have a table with following rows.
FY REVCODE Jul Jun
2015 BNQ 1054839 2000000
2015 FNB 89032 1000000
2015 RS 1067299 3000000
I am looking to convert it to
Month BNQ FNB RS
JUL 1054839 89032 1067299
JUN 2000000 1000000 3000000
I tried with the following and result is coming for one month i.e. JUL but not with the second Month i.e Jun
SELECT 'Jul1' AS MON, [BNQ], [FNB], [RS]
FROM
(SELECT REVENUECODE, SUM(ROUND(((Jul/31)*30),0)) AS JUL
FROM RM_USERBUDGETTBL
WHERE USERNAME='rahul' AND FY=2015
GROUP BY REVENUECODE, USERNAME
) AS SourceTable
PIVOT
(SUM(JUL) FOR REVENUECODE IN ([BNQ], [FNB], [RS])) AS PivotTable
Results:
MONTHBNQ FNB RS
Jul11054839 89032 1067299
SQL Server 2014 :: How To Pivot Output Data Into Multiple Rows
Nov 4, 2015Is it possible to pivot the output data into multiple rows?
I wanted one row for deleted data and another row for Inserted data, I was looking at UNION ALL and CROSS APPLY but to no avail.
PIVOT/CROSS TAB/Converting Rows To (multiple Group) Columns
Aug 3, 2007Hello All,
I am trying to convert the rows in a table to columns. I have found similar threads on the forum addressing this issue on a high level suggesting the use of cursors, PIVOT Transform, and other means. However, I would appreciate if someone can provide a concrete example in T-Sql for the following subset of my problem.
Consider that we have Product Category, Product and its monthly sales information retrieved as follows:
CategoryID
ProductID
ProductName
Month
UnitPrice
QtySold
SalesAmount
1
1
Panel
Jan
5
10
50
1
1
Panel
Feb
5
15
75
1
1
Panel
Mar
5
20
100
1
2
Frame
Jan
10
30
300
1
2
Frame
Feb
10
25
250
1
2
Frame
Mar
10
20
200
1
3
Glass
Jan
20
10
200
1
3
Glass
Feb
20
20
400
1
3
Glass
Mar
20
30
600
I would like it to be converted into following result set:
CategoryID
ProductID
ProductName
UnitPrice
QtySold_Jan
SalesAmt_Jan
QtySold_Feb
SalesAmt_Feb
QtySold_Mar
SalesAmt_Mar
1
1
Panel
5
10
50
15
75
20
100
1
2
Frame
10
30
300
25
250
20
200
1
3
Glass
20
10
200
20
400
30
600
I have purposefully included QtySold here as I need to display both Quantity and Sales as measured column groups in my report. Can this be achieved in sql? I would appreciate any responses.
Thanks.
Problems With PIVOT Tables
Nov 14, 2007I'm a having a problem with PIVOT tables.
I have a table that stores the results from multiple surveys, (see format below)... SurveyID RespondantID QuestionID Answer
=========================================================
1 1 1 X
1 1 2 Y
1 1 3 Z
1 2 1 R
1 2 2 S
1 2 3 T
14 10 1 A
14 10 2 B
14 11 1 C
14 11 2 D
I want to use a pivot table to convert the data it to a format like the following (for surveyID = 1)... RespondantID Q1 Q2 Q3
===============================
1 A B C
1 R S T
The 2 differences I need from a normal pivot query are....
(a) I don't want to summarise my results at all
(b) I have multiple surveys in the same table so need to use an extra clause to pick out info for the survey I am interested in.
So far I have come up with...
SELECT RespondantID, [1] As Q1, [2] As Q2, [3] As Q3, [4] As Q4, [5] As Q5, [6] As Q6, [7] As Q7, [8] As Q8, [9] As Q9, [10]As Q10 FROM (SELECT RespondantlD, QuestionlD, Answer FROM "3_Temp" WHERE SurveylD=3) AS preData PIVOT (COUNT(Answer) FOR QuestionlD IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10]) ) AS data ORDER BV RespondantlD
But it doesn't work and I can't figure out why (error message when executing in VBA = "RunSQL function requires a SQL argument"
Can anyone help?
Pivot Tables In Sql Server???
Sep 7, 2004Hello Everybody,
Can anyone tell me how to create a pivot table in SQL Server?
I am attaching a picture of what the table should look like before and after the transformation. Thank you in advance.
HELP MDX , NAMED SET AND PIVOT TABLES !!
Sep 29, 2005I NEED KNOW IF SOMETHING KNOW CERTANLY .....ABOUT THIS :************************************************** ********************************PLACE A NAMED SET INTO A PIVOT TABLE : ALL ABOUT THIS..FORMULAS...FORMS...EXAMPLES.... ALL !ANOTHER : GET RELATIONSHIP ABOUT THE FIRST TROUBLE : SEE A NAMED SET FROM ACUBE ( OLAP)************************************************** ************************************************REALLY NEED ALL ABOUT YOU CAN SEND ME.AND WRITE ME ....A OLNLY FIN MANYQUESTIONS IN FORUMS....BUT NO ONE RESPONSES.....APARENTLY TOO MUCH PEOPLEHAVE THIS TROUBLE ...... I KNOW ....NO EVERY DAY .MEMBERS THAT POST IN THISWEBSITE..TAKE REPLY...BUT........, REALLY NEED INFO ABOUT .AS SOON ASPOSSIBLE....MI E-MAIL :Join Bytes!THANKS !.MY BEST WISHES...--hi all !
View 1 Replies View RelatedSQL Server 2012 :: Convert Rows To Columns Using Dynamic PIVOT Table
Feb 3, 2015I have this query:
SELECT TOP (100) PERCENT dbo.Filteredfs_franchise.fs_franchiseid AS FranchiseId, dbo.Filteredfs_franchise.fs_brandidname AS Brand,
dbo.Filteredfs_franchise.fs_franchisetypename AS [Franchise Type], dbo.Filteredfs_franchise.fs_franchisenumber AS [Franchise Number],
dbo.Filteredfs_franchise.fs_transactiontypename AS [Transaction Type], dbo.Filteredfs_franchise.fs_franchisestatusname AS [Status Code],
[Code] ....
I need to pivot this so I can get one row per franchiseID and multiple columns for [Franchisee Name Entity] and [Franchise Name Individual]. Each [Franchisee Name Entity] and [Franchise Name Individual] has associated percentage of ownership.
This has to be dynamic, because each FranchiseID can have anywhere from 1 to 12 respective owners and those can be any combination of of Entity and Individual. Please, see the attached example for Franchise Number 129 (that one would have 6 additional columns because there are 3 Individual owners with 1 respective Percentage of ownership).
The question is how do I PIVOT and preserve the percentage of ownership?