Please Help Iain J Unpivoted Pivot Check Each Column Succession-problem X
Apr 20, 2008
hi i need help problem common character "X"or"Z"
the problem is that i must use the character "X"or"Z"
i explain i need to do this
if i put the day1 = "X"or"Z" instead of day1 = 'A' in day1 it be calculated as day1 = 'A'
if i put the day11 ="X"or"Z" instead of day11 = 'B' in day11 it be calculated as day11 = 'B'
if i put the day1 = "X"or"Z" instead of day111 = 'C' in day111 it be calculated as day111 = 'C'
.....
i put example below
Iain J wrote:
Here's a query that will get you the result row you desire
Code Snippet
-- Set up sample data
declare @data table (id int, fname nvarchar(10), val int, day1 nvarchar(1), day11 nvarchar(1), day111 nvarchar(1), day2 nvarchar(1), day22 nvarchar(1), day222 nvarchar(1), day3 nvarchar(1), day33 nvarchar(1), day333 nvarchar(1), day4 nvarchar(1), day44 nvarchar(1), day444 nvarchar(1), day5 nvarchar(1), day55 nvarchar(1), day555 nvarchar(1))
-- Query starts here
select null as id, null as fname, 0 as val,
case day1A when 0 then 'NO' when 1 then 'ok' else cast(day1A as nvarchar) end + '-A' as day1,
case day1B when 0 then 'NO' when 1 then 'ok' else cast(day1B as nvarchar) end + '-B' as day11,
case day1C when 0 then 'NO' when 1 then 'ok' else cast(day1C as nvarchar) end + '-C' as day111,
case day2A when 0 then 'NO' when 1 then 'ok' else cast(day2A as nvarchar) end + '-A' as day2,
case day2B when 0 then 'NO' when 1 then 'ok' else cast(day2B as nvarchar) end + '-B' as day22,
case day2C when 0 then 'NO' when 1 then 'ok' else cast(day2C as nvarchar) end + '-C' as day222,
case day3A when 0 then 'NO' when 1 then 'ok' else cast(day3A as nvarchar) end + '-A' as day3,
case day3B when 0 then 'NO' when 1 then 'ok' else cast(day3B as nvarchar) end + '-B' as day33,
case day3C when 0 then 'NO' when 1 then 'ok' else cast(day3C as nvarchar) end + '-C' as day333,
case day4A when 0 then 'NO' when 1 then 'ok' else cast(day4A as nvarchar) end + '-A' as day4,
case day4B when 0 then 'NO' when 1 then 'ok' else cast(day4B as nvarchar) end + '-B' as day44,
case day4C when 0 then 'NO' when 1 then 'ok' else cast(day4C as nvarchar) end + '-C' as day444,
case day5A when 0 then 'NO' when 1 then 'ok' else cast(day5A as nvarchar) end + '-A' as day5,
case day5B when 0 then 'NO' when 1 then 'ok' else cast(day5B as nvarchar) end + '-B' as day55,
case day5C when 0 then 'NO' when 1 then 'ok' else cast(day5C as nvarchar) end + '-C' as day555
from (select
sum(case when character = 'A' then 1 else 0 end)
+ sum(case when day11 = 'A' then 1 else 0 end)
+ sum(case when day111 = 'A' then 1 else 0 end) as day1A,
sum(case when day1 = 'B' then 1 else 0 end)
+ sum(case when day11 = 'B' then 1 else 0 end)
+ sum(case when day111 = 'B' then 1 else 0 end) as day1B,
sum(case when day1 = 'C' then 1 else 0 end)
+ sum(case when day11 = 'C' then 1 else 0 end)
+ sum(case when day111 = 'C' then 1 else 0 end) as day1C,
sum(case when day2 = 'A' then 1 else 0 end)
+ sum(case when day22 = 'A' then 1 else 0 end)
+ sum(case when day222 = 'A' then 1 else 0 end) as day2A,
sum(case when day2 = 'B' then 1 else 0 end)
+ sum(case when day22 = 'B' then 1 else 0 end)
+ sum(case when day222 = 'B' then 1 else 0 end) as day2B,
sum(case when day2 = 'C' then 1 else 0 end)
+ sum(case when day22 = 'C' then 1 else 0 end)
+ sum(case when day222 = 'C' then 1 else 0 end) as day2C,
sum(case when day3 = 'A' then 1 else 0 end)
+ sum(case when day33 = 'A' then 1 else 0 end)
+ sum(case when day333 = 'A' then 1 else 0 end) as day3A,
sum(case when day3 = 'B' then 1 else 0 end)
+ sum(case when day33 = 'B' then 1 else 0 end)
+ sum(case when day333 = 'B' then 1 else 0 end) as day3B,
sum(case when day3 = 'C' then 1 else 0 end)
+ sum(case when day33 = 'C' then 1 else 0 end)
+ sum(case when day333 = 'C' then 1 else 0 end) as day3C,
sum(case when day4 = 'A' then 1 else 0 end)
+ sum(case when day44 = 'A' then 1 else 0 end)
+ sum(case when day444 = 'A' then 1 else 0 end) as day4A,
sum(case when day4 = 'B' then 1 else 0 end)
+ sum(case when day44 = 'B' then 1 else 0 end)
+ sum(case when day444 = 'B' then 1 else 0 end) as day4B,
sum(case when day4 = 'C' then 1 else 0 end)
+ sum(case when day44 = 'C' then 1 else 0 end)
+ sum(case when day444 = 'C' then 1 else 0 end) as day4C,
sum(case when day5 = 'A' then 1 else 0 end)
+ sum(case when day55 = 'A' then 1 else 0 end)
+ sum(case when day555 = 'A' then 1 else 0 end) as day5A,
sum(case when day5 = 'B' then 1 else 0 end)
+ sum(case when day55 = 'B' then 1 else 0 end)
+ sum(case when day555 = 'B' then 1 else 0 end) as day5B,
sum(case when day5 = 'C' then 1 else 0 end)
+ sum(case when day55 = 'C' then 1 else 0 end)
+ sum(case when day555 = 'C' then 1 else 0 end) as day5C
from @data) x
nedd help from the from the wonderful people here i am stuck in ! i need to check each column succession the chacking is for 3 columns (day1+day11+day111) (day2+day22+day222) (day3+day33+day333) (day4+day44+day444) (day5+day55+day555) and if i don't have the value than add to a new result row
if not show the missing value in the result row at END
if i have in the fields columns A B C than
ok-A ok-B ok-C
if i don't have the value A B C than than show it like this
no-A no-B no-Cif i don't have the value c than than show it like this no-C (on the left )
if i don't have the value a than than show it like this no-A (on middle)
if i don't have the value b than than show it like this no-B (on right)
IF have duplicity a or b or c show in the result row at END 2-C=(thre is tow time the C)
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 have two columns. policyNumber contains a 12-13 varchar string AllPolicyNumbersIncluded contains one or more 12-13 varchar strings (policy nums) seperated by commas
I want to check if policyNumber is contained in AllPolicyNumbersIncluded?
I have policyNumber LIKE AllPolicyNumbersIncluded which works when only one policy number is in AllPolicyNumbersIncluded and incidently works switched around AllPolicyNumbersIncluded LIKE policyNumber I assume because they are equal.
Can anyone tell me how to check if one column's value is a substring of another - without going through every possible substring of the second
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 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'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.
I have written a Query to Pivot this data like below:
SELECT WAREHOUSE,ITEM, QTY
FROM
(SELECT ITEM,WAREHOUSE,FOR1,FOR2,for3,for4,for5,for6,for7,for8,for9,for10, for11,for12,for13,for14,for15,for16,for17,for18,for19,for20,for21, for22,for23,for24 FROM mvxreport.tbldmsForecasttoMovex) p
UNPIVOT
(QTY FOR tbldmsForecasttoMovex IN (FOR1,FOR2,for3,for4,for5,for6,for7, for8,for9,for10,for11,for12,for13,for14,for15,for16,for17,for18,for19, for20,for21,for22,for23,for24))AS unpvt
I would like to add some more code to the query, so for each FOR% column, i can put a numeric value in it. The value will be the numbers ,1 - 24 . One for each line as this represents Months Forward.
Q1-2013 Q2-2013 Q3-2013 Q4-2013 2013 Total Distinct Count Member Distinct Count Member Distinct Count Member Distinct Count Member Member Months AMBULANCE - LAND 264 301 355 352 1272 AMBULATORY SURGICAL CENTER 16 38 30 34 118 COMMUNITY MENTAL HEALTH CENTER 6 7 11 13
I have the above data, and want to create a column adding up the Distinct count member of each quarter. The number of distinct count member of each quarter is from a measure. The result I want to create is the column of 2013 Total Member Months as  shown above. How to create a dax formula.Â
Using PowerPivot I created a connection to a view on a SQL Server database. All fields imported correctly.A column has since been added to the view. How do I get this to appear in my PowerPivot?
how count column in pivot table- and add result row i need to calculate each column for example day1 day2 day3 day4 day5 ------------------------------------------------------------------------- 1 2 1 2 3 1 2 3 2 2 2 3 2 1 2 2 3 0 0 0 -----------------------------------------------------------new result row ok ok 1|2|3 1 3
i need to check each column if i have twice each number if not show the missing number TNX
Code Block DECLARE @Employee TABLE (ID INT, Date SMALLDATETIME, ShiftID TINYINT) DECLARE @WantedDate SMALLDATETIME, -- Should be a parameter for SP @BaseDate SMALLDATETIME, @NumDays TINYINT SELECT @WantedDate = '20080301', -- User supplied parameter value @BaseDate = DATEADD(MONTH, DATEDIFF(MONTH, '19000101', @WantedDate), '19000101'), @NumDays = DATEDIFF(DAY, @BaseDate, DATEADD(MONTH, 1, @BaseDate))
IF @Numdays > 28 BEGIN SELECT p.ID, p.[1] , p.[2],p.[3], p.[4], p.[5], p.[6], p.[7], p.[8], p.[9], p.[10], p.[11], p.[12], p.[13], p.[14], p.[15], p.[16], p.[17], p.[18], p.[19], p.[20], p.[21], p.[22], p.[23], p.[24], p.[25], p.[26], p.[27], p.[28], p.[29], p.[30], p.[31] FROM ( SELECT ID, DATEPART(DAY, Date) AS theDay, ShiftID FROM v_Employee WHERE Date >= @BaseDate AND Date < DATEADD(MONTH, 1, @BaseDate) ) AS y PIVOT ( min(y.ShiftID) FOR y.theDay IN ([1], [2], [3], [4], [5], [6], [7],[8] , [9], [10], [11], [12], [13], [14], [15], [16], [17], [18], [19], [20], [21], [22], [23], [24], [25], [26], [27], [28], [29], [30], [31]) ) AS p END
I have two tables shown below and I wish to add a calculated column to Table 1.
Table1                                     Table2 ID   Activity                            Activity 1    Activity 2   Activity 3   etc   etc  etc  Total 1   Activity 1                          values        values      values                                 Total value 2   Activity 2                          values        values      values                                 Total value 3   Activity 3                          values        values      values                                 Total value etc
I want each row in the new column to show the corresponding activity divided by the sum of the total. So in the row with Activity 3 I want the formula SUM(Table2[Activity 3])/SUM(Table2[Total]). Also the number of activities will vary.
I am actually wanting to calculate a more complicated formula which calculates the correlation .
I am trying to figure out how to pivot a temporary table. I have a table which starts with a date but the number of columns and columns names will vary but will be type INT (Data, col2,col3,col4………….n)
So it could look like
Date , TS-Sales, Budget , Toms sales 01-Jan-14,100,120,300 02-Jan-14,80,150,300 03-Jan-14,100,20,180
What I want is to be able to sum al the columns but without knowing the name and the amount columns to start with this is a manually processes. How could I automate this?
hello, i'm using sql200 and i am attempting to create a table that has an hourly-incrementing 'Date_Time' column, with a corresponding 'Total' column (which keeps a running total of values off of another table) . The code I am using right now is...
declare @date as smalldatetime
set @date = dateadd(yy, -1, cast(convert(char(11), current_timestamp, 101) + '00:00:00' as smalldatetime))
select dateadd(hh, i, @date) as Date_Time, sum(Subtotal) as Total
into #POGtable
from Pivot, OrderGroup
where
i between 0 and 24 and
CreationDate between @date and dateadd(hh, i, @date)
group by i
select dateadd(hh, i, @date) as Date_Time, 0 as Total
into #Ptable
from Pivot
where i between 0 and 24
group by i
select *
from #POGtable
union
select * from #Ptable p
where not exists(
select * from #POGtable pog
where p.Date_Time >= pog.Date_Time)
the solution is ugly, but the problem i'm having is that values for 'SubTotal' don't usually appear before 8 or 9 am. what you see above is me getting all the times (hours) that a subtotal present, creating another table with every possible hour in it (and with a 'Total' column as just zero), and then combining the two tables to create one flowing table over a 24-hour period.
there has GOT to be a better way to do this; the main point being that i want the sum( ) function to start adding up values immediately so i don't have to union two tables
I have written a simple DAX calculated column formula using SEARCH and got an error: It tells the text "PWP" cannot be found but as you can see the screen shows this text exsist within "Promotional Claim"The same happens when I try FIND function.
This is an SPC chart controlled by a slicer that operates a powerpivot table. This is then copied (by cell formula) into a "normal" table where the average, UCL, LCL and Erlang are calculated which are just basic calculations involving average and standard deviation. To make it work the values in the Average, UCL,LCL and Erlang must be repeated all the way down the table to create the chart. In the current format it works well and using a standard table keeps the chart range dynamic.
However this is a very clunky solution involving repeating the data tables in excel. I need to create dozens of charts and it will get large and slow.I would like to create the whole thing in a powerpivot table using measures so i can use powerpivot charts and ultimately powerBI. The data column PasID is a text column so I need a measure that calculates the "count of PasID" for each day(the row labels) and then repeats that value down a whole column in the same way the standard table does. I couldn't figure out how to get the correct number repeat down the whole column, which measures to use,Whether to create calculated columns in the data model or any of it. SO I need to be able to get a count of a text column then display the average of that count in a second column all the way down.
I've been trying to use a simple calculate forumla when adding a column CALCULATE(SUM([Credit]), FILTER(ALL([Date],[Date]<=MAX([Date]))) or just a CALCULATE(SUM([Credit])
And I have always the same error
Calculate is not recognized. I've try it on PowerBI and Excel ...
Can someone pls help me with how to check for a numeric value in a varchar column?
For example I have a column called client_id , it has values "AB" , "CD" , "18", "19" . I need to delete those client_id where the values are 18 and 19. How would I do that?
Can't seem to get my head around this: I'm looking for a way to select only those varchar(10) values that soley consist of numbers. Leading spaces are allowed.
I tried using isnumeric, but it also allows those with periods, comma's etc. Also tried using like, but the length of the varchar column varies too much to do a like '[0-9][0-9]....'.
As a solution I currently do a combo of isnumeric, not like '%.%', not like '%,%' etc. I need to do a conversion to an int to join another table, but the convert still fails. Not sure where and why.
I'm thinking there should be a better way than create a hughe list of "not like " but it looks like I'm in the woods here...
In a stored procedure I have I have dates in the format YYYYMMDD with symbols representing the first 3 digits e.g. °30903 =20030903, and I have to convert them to proper dates, and then eliminate all old data, so I replace symbols and then convert to int
I am trying to query multiple columns for a specific value. I have 8 columns (values are either 1 or 0)and I want to query the table to find out which rows contain zero's in ALL of the 8 columns. Whats the best way to do this? I can create a lenghty select statement where column1 =0 and column2 =0 and column3=0 and column4 =0 and column5 =0 .... etc. I was wondering if there was an easier way to do this?
