Please Help Unpivoted Pivot Check Each Column Succession-and Add Result Row At END
Apr 9, 2008
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)
ignore the character '*' and '-' and Q and Y
id
fname
val
day1
day11
day111
day2
day22
day222
day3
day33
day333
day4
day44
day444
day5
day55
day555
111
aaaa
2
111
aaaa
1
A
NULL
NULL
B
C
NULL
NULL
-
C
-
-
-
C
222
bbbb
2
222
bbbb
1
B
NULL
NULL
C
B
NULL
NULL
B
C
NULL
NULL
333
cccc
2
333
cccc
1
C
NULL
NULL
*
444
dddd
2
444
dddd
1
*
A
B
NULL
NULL
555
EEE
2
-
-
-
-
Q
555
EEE
1
*
*
*
Q
0
ok-A
ok-B
ok-C
ok-A
ok-B
ok-C
ok-A
ok-B
NO-C
NO-A
ok-B
ok-C
NO-A
NO-B
2-C
i have similar code i send it
TNX for the help
View 23 Replies
ADVERTISEMENT
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))
insert into @data
select 111, 'aaaa', 2, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null
union all select 111, 'aaaa', 1, 'A', null, null, 'B', null, null, 'C', null, null, '-', 'C', '-', '-', '-', 'C'
union all select 222, 'bbbb', 2, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null
union all select 222, 'bbbb', 1, 'B', null, null, 'C', null, null, 'B', null, null, 'B', null, null, 'C', null, null
union all select 333, 'cccc', 2, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null
union all select 333, 'cccc', 1, 'C', null, null, '*', null, null, null, null, null, null, null, null, null, null, null
union all select 444, 'dddd', 2, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null
union all select 444, 'dddd', 1, '*', null, null, 'A', null, null, null, null, null, null, null, null, 'B', null, null
union all select 555, 'eeee', 2, '-', '-', '-', null, null, null, null, null, null, null, null, null, '-', null, 'Q'
union all select 555, 'eeee', 1, '*', '*', '*', null, null, null, null, null, null, null, null, null, 'Q', null, null
-- 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
Hope that helps
Iain
View 8 Replies
View Related
Jan 20, 2008
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
View 12 Replies
View Related
Apr 17, 2015
I am querying a table with multiple rows
SELECT caseId, Name ,CompletedDate
FROM TableZ
the number of results per CaseID are varable some have 1 record others up to 36 records..I need to pivot the results based off of the competed date
Name has 20 distinct values but we can have diplicates...so something like
Case Name Date
1 This 2015-01-01
1 That 2015-01-03
2 That 2013-01-01
2 Then 2014-12-03
2 This 2015-01-01
2 There 2015-04-03
3 This 1999-01-01
3 That 2001-01-02
3 This 2006-01-01
looks like this
1 This That
2 That Then This There
3 This That This
View 2 Replies
View Related
Mar 11, 2008
help
i need to check each column
and if i don't have the value
than add to a new result row
if not show the missing value
if i have in the field A B C than OK (like day1 + day2)
if i don't have the value A B C than than show it like this a|b|c (like day3)
if i don't have the value c than than show it like this -|-|c (like day4)
if i don't have the value a than than show it like this a|-|- (like day5)
if i don't have the value b than than show it like this -|b|- (like day6)
day1 day2 day3 day4 day5 day6
-------------------------------------------------------------------------
* c * a c a
* a * a c *
a b # b b c
b * $ Q c s
c # 0 0 0 *
---------------------------------------------------------------------new result row
ok ok a|b|c -|-|c a|-|- -|b|-
TNX
View 7 Replies
View Related
Jan 20, 2005
I have information on clothes in a table that I want to select out to a result set in a different structure - I suspect that this will include some kind of pivot (or cross-join?) but as I've never done this before I'd appreciate any kind of help possible.
Current structure is:
Colour Size Quantity
-----------------------
Red 10 100
Red 12 200
Red 14 300
Blue 10 400
Blue 12 500
Blue 14 600
Green 10 700
Green 12 800
Green 14 900
Green 16 1000
I want to produce this result set:
Colour Size10 Size12 Size14 Size16
-------------------------------------
Red 100 200 300 0
Blue 400 500 600 0
Green 700 800 900 1000
There could be any number of sizes or colours.
Is this possible? Can anyone give me any pointers?
Thanks in advance
greg
View 8 Replies
View Related
May 20, 2015
I have 2 tables (#raw1 & #raw2). Each has a year (yr) and month (mnth) and a count (cnt1 / cnt2) field. I need a table created as follows:
2013 2014 2015
cnt1 cnt2 cnt1 cnt2 cnt1 cnt2
jan 5 77 77 8 88
etc....
Normally, I would ask about a pivot but since it is months down one side and year and columns on the top, I don't know if a pivot will work.
create table #raw1 ([yr] int, [mnth] int, [cnt1] int)
insert into #raw1 values
(2013, 1, 5)
, (2013, 2, 5)
, (2013, 3, 5)
, (2013, 4, 5)
, (2013, 5, 5)
, (2013, 6, 5)
[code]....
View 1 Replies
View Related
Apr 9, 2008
hi need help from the wonderful people here
i am working on project and convert a OLD DOS program to SQL server on the web
this is the resalt i need to show
napipot
Fname
new_unit
day1
day11
day111
day2
day22
day222
day3
day33
day333
day4
day44
day444
1
aaaa
1_1
A
C
Q
D
E
y
2
bbbb
2_2
-
E
A
d
3
cccc
3_3
B
F
B
z
f
4
dddd
1_1
-
*
5
eeee
4_4
C
A
C
--------------------------------------------------------add space row-------------------------
result row
Ok
OK
OK
ok
B
ok
ok
ok
ok
A
B
C
i need check only the
A
B
C
if not show the missing A B C
like this
A on the left
B on middleC on right
View 1 Replies
View Related
Jul 8, 2015
I have some data which is vertical...I want to create a pivot query in SQL that will give me a result that is horizontal like this. I cannot find a way of doing it without lots of IF or CASE statements?
View 10 Replies
View Related
Aug 1, 2007
in table Databackup
company keepmonth
----------------- -------------------
001 12002 12003 6005 607917 609747 6
I run this query
select Max(keepmonth) as keep from Databackup
why the result is 6 not 12? I think the max value should 12 , have no idea why it return 6
does my query error?
thank you
View 1 Replies
View Related
Oct 16, 2015
I have a query to check the records of job has received after 4pm Monday to Friday and it has completed before 9am next day and also weekend it should be Friday after 4pm and before Monday 10am for particular financial year period. I have my job table which has full date but it doesn't show the date exactly Monday to Friday it shows only as 12-12-2014 like that.
View 2 Replies
View Related
Jul 16, 2015
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
[code]....
View 26 Replies
View Related
Feb 27, 2008
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.
View 9 Replies
View Related
Feb 26, 2015
Is there a way to display a column alias as part of the result set column labels?
View 9 Replies
View Related
Oct 3, 2006
Hello,I am trying to follow along with the Data Access tutorial under the the "Learn->Videos" section of this website, however I am running into an error when I try to use the "Edit -> Update" function of the Details View form: I'll post the error below. Any clues on how to fix this? Thanks in advance!!! ~DerrickColumn name 'Assigned_To' appears more than once in the result column list. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Column name 'Assigned_To' appears more than once in the result column list.Source Error: Line 1444: }
Line 1445: try {
Line 1446: int returnValue = this.Adapter.UpdateCommand.ExecuteNonQuery();
Line 1447: return returnValue;
Line 1448: }
View 3 Replies
View Related
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
View 1 Replies
View Related
May 29, 2008
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
View 11 Replies
View Related
Oct 8, 2007
How can I test to see if a column exists before adding a column to a sql mobile table?
thanks,
Luis
View 1 Replies
View Related
Feb 23, 2015
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,
[Code] ....
View 4 Replies
View Related
Apr 6, 2004
hello,
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!
View 5 Replies
View Related
May 13, 2015
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.
View 5 Replies
View Related
Sep 20, 2007
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
View 5 Replies
View Related
Jul 1, 2014
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'
[code]...
View 1 Replies
View Related
Nov 14, 2007
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.
View 9 Replies
View Related
Sep 27, 2007
Hey all,
i have a question for all the SQL Gurus out there. I have tried to think of a way around, it, but i cannot work it out.
I have a set of data: Samples Below:
Item Warehouse FOR1 FOR2 FOR3 FOR4 FOR5 FOR6 FOR7 FOR8 FOR9 FOR10 FOR11 FOR12 FOR13 FOR14
01-0001 010 329 329 335 343 317 331 328 331 31
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
Warehouse Item Qty
010 01-0001 329
010 01-0001 329
010 01-0001 335
010 01-0001 343
010 01-0001 317
010 01-0001 331
010 01-0001 328
010 01-0001 331
010 01-0001 315
010 01-0001 344
010 01-0001 334
010 01-0001 321
010 01-0001 327
010 01-0001 328
010 01-0001 332
010 01-0001 342
010 01-0001 316
010 01-0001 330
010 01-0001 330
010 01-0001 331
010 01-0001 315
010 01-0001 343
010 01-0001 333
010 01-0001 322
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.
Example:
Warehouse Item Qty Month
010 01-0001 329 1
010 01-0001 329 2
010 01-0001 335 3
010 01-0001 343 4
010 01-0001 317 5
010 01-0001 331 6
010 01-0001 328 7
010 01-0001 331 8
010 01-0001 315 9
010 01-0001 344 10
010 01-0001 334 11
010 01-0001 321 12
010 01-0001 327 13
010 01-0001 328 14
010 01-0001 332 15
010 01-0001 342 16
010 01-0001 316 17
010 01-0001 330 18
010 01-0001 330 19
010 01-0001 331 20
010 01-0001 315 21
010 01-0001 343 22
010 01-0001 333 23
010 01-0001 322 24
Does anyone know how i can do this?
Many Thnank
Scotty
View 5 Replies
View Related
Nov 20, 2015
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.
View 5 Replies
View Related
Aug 24, 2015
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?
View 3 Replies
View Related
May 18, 2015
i have a table like below,
CREATE TABLE #ATTTABLE
(
Name VARCHAR(20),
AttDate DATE,
PresntTime TIME
[code]....
and then i pivot table by date as column wise using the below query and also displays total time by rowswise
SELECT t1.*, t2.Total
FROM (
SELECT name,[2015-08-01],[2015-08-02]
FROM (
SELECT name, AttDate,PresentTime
[code]....
now what i need is to display sum of time at last row as well, means total time of against date
View 16 Replies
View Related
Sep 4, 2015
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 .
View 2 Replies
View Related
Jan 17, 2008
Hi
I need help to build the below data to my requirement
i have table having the following data's
NoofLive
Action
Sold
Ratio
EffDt
10-49
43
0
0
1/1/2008
50-99
62
0
0
1/1/2008
100-199
73
0
0
1/1/2008
200-299
17
1
0.059
1/1/2008
300-499
25
0
0
1/1/2008
500-999
21
0
0
1/1/2008
1000+
45
0
0
1/1/2008
100-199
11
1
0.091
2/1/2008
1000+
1
0
0
2/1/2008
100-199
17
0
0
3/1/2008
500-999
2
0
0
3/1/2008
EffDt = (Jan-08,Feb-08,Mar-08)
I want creat result as follows from the above data's
Jan-08
Feb-08
Mar-08
Eligible Lives
Quoted
Sold
Close Ratio
Quoted
Sold
Close Ratio
Quoted
Sold
Close Ratio
10 - 49
43
0
0
0
0
0
0
0
0
50 - 99
62
0
0
0
0
0
0
0
0
100 - 199
73
0
0
11
0
0
0
0
0
200 - 299
17
1
0.059
0
0
0
17
0
0
300 - 499
25
0
0
0
0
0
0
0
0
500 - 999
21
0
0
0
0
0
0
0
0
1000+
45
0
0
1
0
0
2
0
0
Please anybody can help build a query to generate the above result.
Thanks in advance
sthanika
View 1 Replies
View Related
Sep 17, 2014
I am creating dynamic pivot and my column order is as below
[2015-02],[2015-04] [Prior] ,[2014-08],[2014-11]
but i want to display as below:
[Prior],[2014-08],[2014-11],[2015-02],[2015-04]
View 1 Replies
View Related
Dec 18, 2014
I 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
View 3 Replies
View Related
Jan 7, 2015
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
Turned to this
01-jan-14, 02-jan-14, 03-jan-14
100,80,100
120,150,20
300,300,180
Or even just the date and a SUM
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?
View 2 Replies
View Related
