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...
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
I have a single table that consist of 4 columns. Entity, ParamName, ParamsValue and ParamiValue. This table stores normalized Late Fee related parameters for apartments. The Entity field contains a code that identifies the apartment complex. The ParamName in a textual field that contains the name of the parameter that the other 2 fields define the value for; ParamsValue and ParamiValue. If the Late Fee parameter (as named in ParamName is something numerical then the value for that parameter can be found in ParamiValue else its in ParamsValue.
I don't know if 'Pivot' is the correct term to use for describing what I am trying to do because I've looked at the Pivot examples and I don't see how that will work for this. Using the Table and data as provided below, how would I construct a query so that I get 1 row per Entity in which the columns are the ParamsValue or ParamiValue for the ParamName listed in the column header (for the query)?
Below is the DDL to create the table and populate it.
USE [DBA_UTIL] CREATE TABLE [dbo].[PARAMEXAMPLE]( [Entity] [varchar](16) NULL,
I have questions and answers from one table, I need to select questions as column names and answers column values as the results for the questions column.
Is there a way we can get Table and Column name in separate column using PIVOT or something?Right now what i have is:
Text QueryPlan Plan_handle Name Value
select id,name,Address from person <showPlznXML... 010101 Table Person select id,name,Address from person <showPlznXML... 010101 column id select id,name,Address from person <showPlznXML... 010101 Table Person
Hi there, I am a new member of this site and I am not very much aware of T-sql's working. My question is what if I need to get one column's data to be the heading of another column. To be very exact I have a school's database. The table I am talking about is of the results of students. The table contains Student ID, Subject ID, Total marks of the subject, Marks obtained in the subject. Now I want to print a report by generating data from this table. Right now the data is something like this StuID - - - SubID - - - -Tot - - -Obt 1 - - - - - - -1 - - - - - - -50 - - - 38 1 - - - - - - -2 - - - - - - -50 - - - 41 1 - - - - - - -3 - - - - - - -50 - - - 42 1 - - - - - - -4 - - - - - - -50 - - - 40 2 - - - - - - -1 - - - - - - -50 - - - 35 2 - - - - - - -2 - - - - - - -50 - - - 40 2 - - - - - - -3 - - - - - - -50 - - - 42 2 - - - - - - -4 - - - - - - -50 - - - 41
StudentID and SubjectID fields are related to other tables so I can get the names from there but when I need the report I need the data in the form of StuID - Sub 1 - - - Sub 2 - - - Sub 3 - - - -Sub4 1 - - - - 38 - - - - - - 41 - - - - - - 42 - - - - - - 40 2 - - - - 35 - - - - - - 40 - - - - - - 42 - - - - - - 41
The Subjects can be different for different students so the query should be dynamic instead of hard coding the names of the subjects. I hope I am clear with my question. The subjectIDs or their names will become the headings and they will contain the obtained marks for that subjects in their columns just for the reports. I have also checked the PIVOT function but was not able to do what I wanted. Thanks.
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 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 (...) )
I 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.
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.
I 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?
This 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 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:
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.
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?
I 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:
I am trying to calculate how much revenue we may get, based on potential new business opportunities. The core fields we have are
Total Contract Value ($ or £)Duration of contract (months)Revenue start dateVarious information about the new business - ID, Title, Customer etc.
We can easily calculate the revenue per month with "Total Contract value divide by duration".
However what I would really like to do is be able to know how much revenue we will be getting each month.
To do this I was thinking we should probably create a new row for each month entry, with the mm-yyyy being the only difference for each row. But how to create the appropriate months and the correct amount of rows.
I've encountered an issue where the ALLSELECTED function works fine unless I use a date hierarchy, i.e. year, quarter, month, date, for the rows in a pivot and then use a year slicer, select one or more years individually, and the clear the filter on the slicer. The year(s) that I selected in the slicer remain at 100% in the pivot instead of returning to the subtotals for the unfiltered slicer.
This only occurs when I use a calendar hierarchy for rows and a date field for the slicer, either from the hierarchy or a regular date field.Below are images of the normal behavior and then the result after following the steps above. Can't figure out why the calendar hierarchy is causing the issue since it works for all other time functions, etc., and follows best practices such as contiguous dates, etc.
This SQL Select below doesn't show the total value. Always shows NULL. Why?
SELECT Ano, FORMAT((QPivot.[1]),'##,##0.00','pt-BR') As trimestre1, FORMAT((QPivot.[2]),'##,##0.00','pt-BR') As trimestre2, FORMAT((QPivot.[3]),'##,##0.00','pt-BR') As trimestre3, FORMAT((QPivot.[4]),'##,##0.00','pt-BR') As trimestre4, Municipio,
I'm trying to Pivot and I keep getting an "Invalid Column Name" error, which I can't figure out since, if I run the query and exclude the Pivot statement, the query runs fine.
Columns ItemNmbr Char(31) not null SetupTime_I Numeric(19,5) not null WCID_I Char(11) not null select ItemNmbr,SetupTime_I, WCID_I from RT010130 Results
Now run select ItemNmbr,SetupTime_I, WCID_I from RT010130 pivot (sum(SetupTime_I) for WCID_I in ([BLA01],[URE02])) PVT
And I get an Invalid Column Name error for both SetupTime_I and WCID_I - which, as far as I can tell, is demonstrably incorrect.
Hi All, I have never used PIVOT before but looks exactly what I want for this scenario: I have rows of dates associated with ID of Hotels and Room avalability for each Hotel/Date..... I want to show the sum of the rooms per date as columns I am using something like this:SELECT dbHotelID ,[09/20/2007]as [Today],[09/21/2007]as [Today+1],[09/22/2007]as [Today+2] FROM vwRoomAvailable PIVOT (SUM(dbRoomNumber) FOR AvailableDate IN ([09/20/2007], [09/21/2007], [09/22/2007])) AS pAs you can see I know how may days I want in advance so know how many columsn so its not dynamic.. I just dont know what the dates are:I would like to do something like: DECLARE @todayDate varchar(255), DECLARE @todayPlusOne varchar(255), DECLARE @todayPlusTwo varchar(255) SET @todayDate = CONVERT(CHAR, GETDATE(),101)SET @todayPlusOne = CONVERT(CHAR, DATEADD(d, 1, GETDATE(),101)SET @todayPlusTwo = CONVERT(CHAR, DATEADD(d, 2, GETDATE(),101) SELECT dbHotelID,@todayDate as Today,@todayPlusOne as [Today+1],@todayPlusTwo as [Today+2] FROM vwRoomAvailable PIVOT (SUM(dbHotelRoomAvailabilityNumber) FOR AvailableDate IN ([@todayDate], [@todayPlusOne], [@todayPlusTwo])) AS pBut I can’t seem to put the variable in the PIVOT value list or GETDATE() Anyone got any ideas or do I just try and do this another way and forgot PIVOT. I am using sql server 2005 express. Thanks in advance. Lee
I have the following SQL which i want to convert to a stored procedure having dynamic SQL to generate column numbers (1 to 52) for Sale_Week.Also, I want to call this stored procedure from Excel using VBA, passing 2 parameters to stored procedure in SQL Server e.g,
DECLARE @KPI nvarchar(MAX) = 'Sales Value with Innovation' DECLARE @Country nvarchar(MAX) = 'UK'
I want to grab the resultant pivoted table back into excel. how to do it?
USE [Database_ABC] GO DECLARE @KPI nvarchar(MAX) = 'Sales Value with Innovation' DECLARE @Country nvarchar(MAX) = 'UK'
I am trying to work on a database with 3 tables. To make it easier I have created a couple of temp tables to work out the syntax.
CREATE TABLE #owner ( [NameId] tinyint IDENTITY(1,1) NOT NULL, [Name] varchar(50) NOT NULL )
INSERT INTO #owner VALUES ('ME'); INSERT INTO #owner VALUES ('Other');
CREATE TABLE #propertyType ( [TypeId] tinyint IDENTITY(1,1) NOT NULL, [Name] varchar(50) NOT NULL )
INSERT INTO #propertyType VALUES ('Home'); INSERT INTO #propertyType VALUES ('Car');
CREATE TABLE #property ( [NameId] tinyint NOT NULL, [TypeId] tinyint NOT NULL, [Value] varchar(50) NOT NULL )
INSERT INTO #property VALUES (1,1, 'Blue'); INSERT INTO #property VALUES (1,2, 'Black'); INSERT INTO #property VALUES (2,1, 'Red'); INSERT INTO #property VALUES (2,2, 'Black');
DROP TABLE #owner; DROP TABLE #propertyType; DROP TABLE #property
| NameId | Name | | 1 | ME| | 2 | other |
| TypeId | Name | | 1 | Home | | 2 | Car |
| NameId | TypeId | Value | | 1 | 1 | Blue | | 1 | 2 | Black | | 2 | 1 | Red | | 2 | 2 | Black |
Where property value is some arbitrary detail. The real propertyType has 50 or 60 rows and not every property has all of the values. I am trying to create a pivot table that would look like so that I can present the data in an easier to understand format:
[Owner | Home | Car ] [ME | Blue | Black ] [Other| Red | Black ]
The propertyTypes are added often, and I don't really have the ability to change them. There is a unique constrant on property on nameid and typeid so there will never be two of the same property with the same owner. Any help would be very helpful.
