hi,
how can I use a select statement in the IN part of the pivot sql instead of ([1],[2],[3])
eg,
I want to do :
PIVOT ( sum(numbers) FOR surname IN (Select surname from person)
that doesn't work though.
any ideas.?
thank you
Hi all,
In MyDatabase, I have a TABLE dbo.LabData created by the following SQLQuery.sql:
USE MyDatabase
GO
CREATE TABLE dbo.LabResults
(SampleID int PRIMARY KEY NOT NULL,
SampleName varchar(25) NOT NULL,
AnalyteName varchar(25) NOT NULL,
Concentration decimal(6.2) NULL)
GO
--Inserting data into a table
INSERT dbo.LabResults (SampleID, SampleName, AnalyteName, Concentration)
VALUES (1, 'MW2', 'Acetone', 1.00)
INSERT €¦ ) VALUES (2, 'MW2', 'Dichloroethene', 1.00)
INSERT €¦ ) VALUES (3, 'MW2', 'Trichloroethene', 20.00)
INSERT €¦ ) VALUES (4, 'MW2', 'Chloroform', 1.00)
INSERT €¦ ) VALUES (5, 'MW2', 'Methylene Chloride', 1.00)
INSERT €¦ ) VALUES (6, 'MW6S', 'Acetone', 1.00)
INSERT €¦ ) VALUES (7, 'MW6S', 'Dichloroethene', 1.00)
INSERT €¦ ) VALUES (8, 'MW6S', 'Trichloroethene', 1.00)
INSERT €¦ ) VALUES (9, 'MW6S', 'Chloroform', 1.00)
INSERT €¦ ) VALUES (10, 'MW6S', 'Methylene Chloride', 1.00)
INSERT €¦ ) VALUES (11, 'MW7', 'Acetone', 1.00)
INSERT €¦ ) VALUES (12, 'MW7', 'Dichloroethene', 1.00)
INSERT €¦ ) VALUES (13, 'MW7', 'Trichloroethene', 1.00)
INSERT €¦ ) VALUES (14, 'MW7', 'Chloroform', 1.00)
INSERT €¦ ) VALUES (15, 'MW7', 'Methylene Chloride', 1.00)
INSERT €¦ ) VALUES (16, 'TripBlank', 'Acetone', 1.00)
INSERT €¦ ) VALUES (17, 'TripBlank', 'Dichloroethene', 1.00)
INSERT €¦ ) VALUES (18, 'TripBlank', 'Trichloroethene', 1.00)
INSERT €¦ ) VALUES (19, 'TripBlank', 'Chloroform', 0.76)
INSERT €¦ ) VALUES (20, 'TripBlank', 'Methylene Chloride', 0.51)
GO
A desired Pivot Table is like:
MW2 MW6S MW7 TripBlank
Acetone 1.00 1.00 1.00 1.00
Dichloroethene 1.00 1.00 1.00 1.00
Trichloroethene 20.00 1.00 1.00 1.00
Chloroform 1.00 1.00 1.00 0.76
Methylene Chloride 1.00 1.00 1.00 0.51
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
I write the following SQLQuery.sql code for creating a Pivot Table from the Table dbo.LabData by using the PIVOT operator:
USE MyDatabase
GO
USE TABLE dbo.LabData
GO
SELECT AnalyteName, [1] AS MW2, AS MW6S, [11] AS MW7, [16] AS TripBlank
FROM
(SELECT SampleName, AnalyteName, Concentration
FROM dbo.LabData) p
PIVOT
(
SUM (Concentration)
FOR AnalyteName IN ([1], , [11], [16])
) AS pvt
ORDER BY SampleName
GO
////////////////////////////////////////////////////////////////////////////////////////////////////////////////
I executed the above-mentioned code and I got the following error messages:
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'TABLE'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'AnalyteName'.
I do not know what is wrong in the code statements of my SQLQuery.sql. Please help and advise me how to make it right and work for me.
Thanks in advance,
Scott Chang
I 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.
Can 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.
I have a simple pivot table (screenshot below) that has two variables on it: one for entry year and another for 6 month time intervals. I have very simple DAX functions that count rows to determine the population N (denominator), the number of records in the time intervals (numerator) and the simple percent of those two numbers.
The problem that I am having is that the function for the population N is not overriding the time interval on the pivot table when I use an ALL function to do so. I use ALL in other very simple pivot tables to do the same thing and it works fine.
The formula for all three are below, but the one that is the issue is the population N formula. Why ALL would not work, any other way to override the time period variable on the pivot table.
Population N (denominator):
=CALCULATE(COUNTROWS(analyticJudConsist),ALL(analyticJudConsist[CurrentTimeInCare1]))
Records in time interval (numerator):
=COUNTROWS(analyticJudConsist)
Percent:
=[countrows]/[denominatorCare]
How can I apply "Min" formula under a "new measure" (calculated field) within a pivot table under Power pivot 2010?Can see that neither does it allow me to apply "min" formula directly "formula box" nor could find any other option.Intent formula: "=Min(1,sum(a:b))" this isn't allowed so all I can do is "=sum(a:b)".
View 3 Replies View RelatedI have simple pivot table (below screenshot with info redacted) that displays a population number ("N" below), this is the denominator, a cumulative numerator number (below "#") and a simple cumulative percent that just divides the numerator by the denominator. It cumulates from top to bottom. The numerator and percent are cumulative using the below functions. There are two problems with the numerator and percent:
1. When there is not a number for the numerator, there is no value displayed for both the numerator and the percent..There should be a zero displayed for both values.
2. When there has been a prior number for the numerator and percent (for a prior month interval) but there is no number for the numerator in the current month interval, the prior month number and percent are not displayed in the current month interval--see the 3rd yellow line, this should display "3" and "16.7%" from the second yellow line.Here is the formula for the numerator:
=CALCULATE(count(s1Perm1[entity_id]),FILTER(ALL(s1Perm1[ExitMonthCategory]),s1Perm1[ExitMonthCategory] <= MAX(s1Perm1[ExitMonthCategory])))
Here is the formula for the percent:
=(CALCULATE(countrows(s1Perm1),FILTER(ALL(s1Perm1[ExitMonthCategory]),s1Perm1[ExitMonthCategory] <= MAX(s1Perm1[ExitMonthCategory]))))/(CALCULATE(COUNTROWS(s1Perm1),ALL(s1Perm1[Exit],s1Perm1[ExitMonthCategory])))
I have data in my Powerpivot window which was generated by a sql query. This data includes a field named 'Cost' and every row shows a value for 'Cost' greater than zero. The problem is that when I display this data in the pivot table all entries for Cost display as $0. At first I thought that maybe Cost was set to a bogus data type (such as 'text) but it is set to ''Decimal Number' so that's not the problem.Â
What is happening and how do I fix it so that my pivot table reflects the values for 'Cost'?
I have a data table that contains budget and actual data by month. Â I use the data to create a pivot that shows actual results next to budgeted results. Â I need a column that shows that variance between those columns. Â I think my issue is that the "Type" field contains actual and Budget. Â I sum on "Type". Â I can't seem to create a sum since those items are in the same field or am I missing something?
Table design
Month|Division|Subdivision|Type|Dept|Rate|Units|Amount
October|DC|Day|Budget|125|10.00|100|1000
October|DC|Day|Actual|125|10.00|110|1100
Output Design
DC
DAY
Actual
Budget
125 AvgOfRate
AvgOfRate
SumOfUnits
SumOfUnits
SumOfAmt
SumOfAmt
How to get a list of values to actually display in correct order in either a slicer or when on an axis on a pivot table?
I currently have the below list and have tried to add a preceding numeric (ex. "1. <=0") or preceding blank space, neither of which is visually great. Is there another way?
<= 0
1 - 6
7 - 12
13 - 18
19 - 24
25 - 30
31 - 36
37 - 42
43 - 48
49 - 54
55 - 60
61 - 66
67 - 72
73 - 78
79 - 84
85 - 90
91 - 96
97 - 102
> 102
I am using excel 2010 and creating pivot table from Power Pivot. I created a pivot table with department slicers. All is good, the problem I am having is whilst in an unfiltered position (ALL) of the slicers (departments) I get 200 pages, now when I click on a given department with say 10 pages, I still get the same 200 pages with the first 10 pages showing the data from the clicked department and 190 blank pages.
All I want is to get a WYSIWYG (What you see is what you get) of what is on the screen as my print, but I am getting extra blank pages right after the data. Â How do I resolve this.
Below are the steps I go thru to printÂ
1. Select slicers in unfiltered position (ALL)
2. Select entire pivot table
3. Select Page layout and select print area.
4. Save
5. Click on Print Preview to preview the print
6. Click on a given department in the slicer and repeat item 5, but this gives me blank pages after the data.
Do I need any other step?Â
I am using the pivot task to to a pivot of YTD-Values and after that I use derived columns to calculate month values and do a unpivot then.
All worked fine, but now I get this error message:
[ytd_pivot [123]] Error: Duplicate pivot key value "6".
The settings in the advanced editor seem to be correct (no duplicate pivot key value) and I am extracting the data from the source sorted by month.
Could it be a problem that I use all pivot columns (month 1 to 12) in the derived colum transformation and they aren´t available at this moment while data extracting is still going on?
any hints?
Cheers
Markus
Say, I have the following temporary table (@tbl) where the QuestionID field will change values over time
Survey QuestionID Answer
1 1 1
1 2 0
2 1 1
2 2 2
I'd like to perform a pivot on it like this: select * from @tbl Pivot (min(Answer) for QuestionID in ([1], [2])) as PivotTable
...however, I can't just name the [1], [2] values because they're going to change.
Instead of naming the values like this:
for QuestionID in ([1], [2], [3], [4])
I tried something like this:
for QuestionID in (select distinct QuestionID from @tbl)
but am getting a syntax error. Is it possible to set up a pivot like this:
select * from @tbl Pivot (min(Answer) for Question_CID in (select distinct @QuestionID from @tbl)) as PivotTable
or does anyone know another way to do it?
Is it possible to generate automatic refresh of excel 2013 table which displays some table of a power pivot model on file open?? I dont want to use pivottable (which supports this ...)
View 2 Replies View RelatedI have a pivot table that connects to our data warehouse via a PowerPivot connection. Â The data contains a bunch of comment fields that are each between 250 and 500 characters. Â I've set the columns in this pivot table to have the 'Wrap Text' set to true so that the user experience is better, and they can view these comment fields more clearly.
However, whenever I refresh the data, the text wrapping un-sets itself. Â Interestingly, the 'Wrap Text' setting is still enabled, but I have to go and click it, then click it again to actually wrap the text. Â This is very burdensome on the user, and degrading the experience.
Any way to make this text wrapping stick so that I don't have to re-set it every time I refresh the data?
Hi,
I've a table like,
ID Key Day Amount1 Amount2 Amount3 Amount4
1 100 1 0.00 7.00 0.00 2.00
2 100 2 8.00 0.00 0.00 0.00
3 100 3 0.00 6.00 0.00 0.00
I want to convert this table using Pivot like,
Key Day1 Day2 Day3 Amount
100 0.00 8.00 0.00 Amount1
100 7.00 0.00 6.00 Amount2
100 0.00 0.00 0.00 Amount3
100 2.00 0.00 0.00 Amount4
How can i do this? Pls explain me with query.
Thanks in advance
Jasmeeta
Hello all,
I am having some issues. Let me set the scene for you:
Table1:
Table2:
I hope you can get what's going on from those pics.
I'm trying to construct a datatable. the user specifies a date range, a bin no and a screen no. From that information, for each date in the date range, i want to show the added weight of each sieve for that particular bin and screen.
I hope this is at least a little clear.I really could use some help on this. thanks.
I have a table structure like this
Vendor Date Item
A1 04/21/2008 T1
A1 04/21/2008 T1
A1 04/21/2008 T1
C1 04/21/2008 T1
C1 04/21/2008 T1
D1 04/21/2008 T1
A1 04/11/2008 T1
A1 04/11/2008 T1
B1 04/11/2008 T1
C1 04/11/2008 T1
D1 04/11/2008 T1
D1 04/11/2008 T1
A1 04/1/2008 T1
A1 04/1/2008 T1
B1 04/1/2008 T1
B1 04/1/2008 T1
C1 04/1/2008 T1
D1 04/1/2008 T1
I want the result like
(date calculated from todays date 04/25/2008)
Vendor Count of item Count of item Count of item
last 10days last 20days last 25days
A1 3 5 7
B1 0 1 3
C1 2 3 4
D1 1 3 4
How can i write the query for this. Please help me ,
Mathew
Hi everyone,I have 3 tables:Telbook(Id:int,Name:char,address:char,comment:ntex t,owneruserid:int),PK:idTelNumbers(telbookid:int,telno:char,telNotype:int, syscode:int),PK:syscode,F*K:telNumbers.telbookid=t elbook.idTeltypes(teltypes:char,fk:int),FK:telnumbers.telno type=teltypes.fkThe question is here that I can create a query which results are:(id,Name,telno,telnotype,teltypes,address,comment) (4,nassa,091463738,2,Mobile,XXX,Null)(4,nassa,071163738,1,Tellphone,XXX,Nul)But,I want a query which shows the results in a way below:(id,Name,tellephone,mobile,Fax,e-mail,address,comment)(4,nassa,071163738,091463738,Null,Null,XXX,Null)I run SQL server 2005, and I want to use PIVOT but I dnt know how!.Thanks,Nassa
View 3 Replies View Related
ID SUBID Count
1 4 23
1 5 10
1 6 5
2 3 4
2 2 7
Is it possible to create a pivot table to put subid where ID =1 to rows and subid where ID =2 to column
3 2
4
5
6
But I am not sure if it's possible to do count with this structure?
Any input would be appreciated..
Hi,
I would like to write a sql query in sql server 2005 to derive a table called tblResult from tblData as follows:
I think I should use unpivot.
tblData
Notice the dates are fields
surname 26 Dec 27 Dec 28 Dec 02 Jan 04 Jan 05 Jan
Brown 100.2 12.65 43.76 3.54 98.12 56.76
Jackson 32.21 98.34 45.54 2.65 65.11 78.86
Peterson 32.23 65.34 88.22 8.34 12.22 87.55
This is what I would like to end up with
tblResult
Surname Date price1 Price2
Brown 26 Dec 100.2 100.2
Jackson 26 Dec 32.21 32.21
Peterson 26 Dec 32.23 32.23
Brown 27 Dec 12.65 12.65
Jackson 27 Dec 98.34 98.34
Peterson 27 Dec 65.34 65.34
Brown 28 Dec 43.76 43.76
Jackson 28 Dec 45.54 45.54
Peterson 28 Dec 88.22 88.22
.
.
.
Thanks
I have the follwing columns in a table and I have tried to run the pivot column over it without much success.
CustomerID
ColumnID
ColumnValue
ColumnDate
Sample Data
TableA
CustomerID ColumnID ColumnValue ColumnDate
100 1 50 10/10/2007
100 2 Today 10/11/2007
100 3 P.O.Box 10/12/2007
101 1 60 10/10/2007
101 5 77 10/11/2007
101 6 PostOffice 10/12/2007
102 1 88 10/10/2007
I have a look up table which tells me the column name as follows
TableB
ColumnID ColumName
1 Sales
2 DayValue
3 AddressType
4 SalesTarget
5 AverageSales
6 CollectionPoint
My data source cotains the following query
Select CustomerID, b.ColumnName, a.ColumnValue, a.ColumnDate
FROM TableA inner join Table B ON a.ColumnID = b.ColumnID
I then pass this through my pivot expecting the following results as a sample
CustomerID Sales SalesDate DayValue DayValueDate
100 50 10/10/2007 Today 10/11/2007
101 60 10/10/2007 Null Null
102 88 10/10/2007 Null Null
I have set up my Pivot as follows:
CustomerID: PivotUsage=1
ColumnValue: PivotUsage=3
ColumnDate: PivotUsage = 3
ColumnName PivotUsage = 2
I am not getting the following results instead;
CustomerID Sales SalesDate
100 50 10/10/2007
101 60 10/11/2007
100 Today 10/10/2007
101 Null Null
Does anyone know why?
Thanks for any help
Greetings SSIS friends,
Consider the following example :
I have the following data :
betting_opportunity----------result_number----------result_id
559632-----------------------4----------------------2763415
559632-----------------------0----------------------2763416
456617-----------------------1----------------------2763423
456617-----------------------3----------------------2763424
what I want to do is display the data like this :
betting_opportunity----------final_result
559632-----------------------(4-0)
456617-----------------------(1-3)
I have done the SQL code for this no problem but I want to do this using SSIS components but I am totally stuck!
Any advice would be appreciated!
Hello, I have the folowing sql query;
SELECT DateTime, TagName, Value
FROM v_AnalogHistory
WHERE (DateTime >= CAST(CONVERT(VARCHAR(8), GETDATE() - 1, 112) AS DATETIME)) AND (DateTime < CAST(CONVERT(VARCHAR(8), GETDATE(), 112)
AS DATETIME)) AND (TagName = N'FI702A') OR
(TagName = N'FI701A') OR
(TagName = N'FI504A') OR
(TagName = N'ZI504A') OR
(TagName = N'ZI502A') OR
(TagName = N'LI103A')
What I would like to know is how to change this query to a pivot query to produce the following report;
Gas
Flow to
Synagro
Flow
to Gas
Burner
Gas Holder
Liq. Overflo
Rate
Gas Holder
Roof
Position
ESD# 1
Liquid
Level
DateTime
FI702A
FI701A
FI504A
ZI502A
LI103A
12 AM
69.6
11703.5
3.9
0.2
60.1
1 AM
69.4
11703.5
3.8
0.2
60.2
2 AM
69.3
11703.5
3.7
0.2
60.4
3 AM
69.6
11703.5
3.7
0.2
60.2
4 AM
69.5
11703.5
3.6
0.2
59.7
5 AM
69.4
11703.5
3.6
0.2
59.2
6 AM
69.5
9363.7
3.6
0.2
58.7
7 AM
69.4
3.8
3.6
0.2
57.6
8 AM
167.3
3.8
3.7
0.2
57.9
9 AM
297.2
3.2
3.7
0.2
57.7
10 AM
327.8
2.9
3.7
0.2
57.4
11 AM
295.2
454.7
3.6
0.2
57.2
12 PM
299.1
1106.6
3.5
0.2
57.1
1 PM
301.3
461.2
3.4
0.2
57.2
2 PM
69.6
3.2
3.4
0.2
57.1
3 PM
69.7
1.8
3.5
0.2
57.2
4 PM
69.7
3.8
3.6
0.2
58.5
5 PM
69.6
1.5
3.7
0.2
59.1
6 PM
174.5
2.3
3.8
0.2
59.0
7 PM
328.8
2.0
3.7
0.2
58.9
8 PM
300.0
2.6
3.6
0.2
58.7
9 PM
240.0
2.3
3.5
0.2
58.2
10 PM
69.6
3.5
3.5
0.2
57.9
11 PM
69.7
2.9
3.6
0.2
57.6
I have a table structure like this
Vendor Date Item
A1 04/21/2008 T1
A1 04/21/2008 T1
A1 04/21/2008 T1
C1 04/21/2008 T1
C1 04/21/2008 T1
D1 04/21/2008 T1
A1 04/11/2008 T1
A1 04/11/2008 T1
B1 04/11/2008 T1
C1 04/11/2008 T1
D1 04/11/2008 T1
D1 04/11/2008 T1
A1 04/1/2008 T1
A1 04/1/2008 T1
B1 04/1/2008 T1
B1 04/1/2008 T1
C1 04/1/2008 T1
D1 04/1/2008 T1
I want the result like (date calculated from todays date 04/25/2008)
Vendor Count of item Count of item Count of item
last 10days last 20days last 25days
A1 3 5 7
B1 0 1 3
C1 2 3 4
D1 1 3 4
How can i write the query for this. Please help me ,
Mathew
Thanks to Adam Haines I've been able to grab DBF FIle data very cleanly into SQL. Now, I need to pivot it for presentation in a SharePoint webpart.
Here's my table.
Employee Name, Date, Department, Hours Worked.
EmployeeA, 5/11/08, Finance, 8
EmployeeA, 5/12/08, Marketing, 4
EmployeeA, 5/12/08, Finance, 4
EmployeeB, 5/11/08, Operations, 9
EmployeeB, 5/12/08, Operations 8
*note: an employee can work in multiple departments on the same day.
What's I'd like, is to pivot this information such that:
Employee, 5/11/08, 5/12/08, 5/13/08, 5/14/08, 5/15/08,...etc for current week., Hours worked, OT Worked
EmployeeA, 8, 9, 7, 8, 10, 42, 2
EmployeeB, 7, 7, 8, 8, 8, 38, 0
OT Would be CASE WHEN (Total - 40 < 0 THEN 0 ELSE Total -40) , I believe.
Now I know holding this information in SQL is not good practice (thanks), but how bout just creating it on demand or in a view? Ultimately it would make my life immensely easier if I could, and then just drop it into my data view in SharePoint.
Thanks in advance!
-Curt
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
Perhaps I was dreaming, but I thought I remembered reading in some SQL Server 7 propaganda that PIVOT TABLE was added. The idea is, a cool syntax that would turn a number of rows into the same number of columns, and automatically transpose the values for you.
This is not a data-mining application. It's more a hyper-normalizing operation. We define products as a collection of attributes that are stored in rows, not columns. A single product is therefore one row from a Product Header table and N rows from the Product Attributes table. Products can have widely differing numbers of attributes.
I know you can write a query that will grab N rows and turn them into N columns, but I don't want to have to write one for every product type (meaning for N, O, P, Q, R and S attributes).
I just looked in one of my SQL Server 7 books and found nothing on Pivot Table, so maybe I was dreaming.
Failing this cool new command, do you know of a general solution to this type of problem?
Thanks in advance,
Arthur Fuller
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!
Hi everybody,
I am a bit of a SQL Server 2005 newbie. I have to run a report extracting survey responses for my group using SQL Server. My question is what is the best way to organize the data so it converts from rows into columns. I know SQL Server was not meant for creating presentation reports but I have over 800 surveys that I compile so any help would be appreciate. I have to extract data from 2 tables within a view.
For example, I would like the data show in this format:
Last Name|First Name|Question1|Question2|Question3|..etc
Doe|John|answer1|answer2|answer3|...etc.
Doe|Jill|answer1|answer2|
Currently it shows in the this format:
Doe|John|Question1|answer1
Doe|John|Question2|answer2
Doe|Jill|Question1|answer1
Doe|Jill|Question 2|answer2
Here is my query. I just need to know how to just get started and I can probably do the rest.
SELECT Distinct
dbo.uvwReporting_SurveyAnswers.DateCreated AS DateCreated
,dbo.uvwReporting_SurveyAnswers.questionid AS QuestionID
,dbo.uvwReporting_SurveyAnswers.userid AS UserID
,dbo.uvwReporting_SurveyAnswers.surveyid AS SurveyID
,dbo.uvwReporting_User.LastName1 AS LastName
,dbo.uvwReporting_User.FirstName AS FirstName
,dbo.uvwReporting_SurveyQuestions.ordernumber AS OrderNumber
,dbo.uvwReporting_SurveyQuestions.QuestionText AS QuestionText
,dbo.uvwReporting_SurveyAnswers.QuestionAnswer AS QuestionAnswer
FROM
dbo.uvwReporting_Surveys
INNER JOIN dbo.uvwReporting_SurveyQuestions
ON dbo.uvwReporting_Surveys.surveyid = dbo.uvwReporting_SurveyQuestions.surveyid
INNER JOIN dbo.uvwReporting_SurveyAnswers
ON dbo.uvwReporting_SurveyQuestions.QuestionID = dbo.uvwReporting_SurveyAnswers.QuestionID
INNER JOIN dbo.uvwReporting_User
ON dbo.uvwReporting_SurveyAnswers.userid = dbo.uvwReporting_User.userid
WHERE
dbo.uvwReporting_SurveyAnswers.surveyid = 100
Order by dbo.uvwReporting_SurveyAnswers.DateCreated
,dbo.uvwReporting_SurveyQuestions.ordernumber
Thanks
MickeyD
I have data in this format
Product qty
ABC 4
DEF 5
:
:
XYZ 4
I'll never have more that 10 products at a time.
I need to deliver the data in a format like this:
prod_1 qty1 prod_2 qty2 ... prod_10 qty10
ABC 4 DEF 5 XYZ 4
If there are less than 10 products, the last columns will contain NULL.
What I'm doing is delivering a recordset to a report.
The report is designed to work with a single record, not multiple lines.
Of course, there's more data than this, this is just where I'm stuck.
Is there a way to pivot this data? I'm using SQL2000
Here's a ddl of what I've been trying to work with,
but all attempts at pivoting have been = FAIL
CREATE TABLE #TMP (
PKG_CODE VARCHAR(20),
QTY_ACT INTEGER)
INSERT INTO #TMP
SELECT 'PAD', 15
UNION ALL
SELECT 'PAD4436', 15
UNION ALL
SELECT 'SW', 15
UNION ALL
SELECT 'TS', 16
SELECT * FROM #TMP
DROP TABLE #TMP
Is it possible to create a pivot list, instead of hardcoding, without having to build it with dynamic sql ? ie:select * from (select Period, loan_id, mba_stat from lp_cq where loan_id = 'AaAaCz') apivot (max(mba_stat) for period IN (select cast(period as varchar) from lp_cq where loan_id = 'AaAaCz')) binstead ofselect * from (select Period, loan_id, mba_stat from lp_cq where loan_id = 'AaAaCz') apivot (max(mba_stat) for period IN ([1], [2],..[200])) b
View 1 Replies View RelatedI have the following data structure (simplified)
declare @log table (
date smalldatetime,
category char(3),
value1 int,
value2 int)
insert into @log(date, category, value1, value2)
select '2008-01-01', 'ABC', 11,12 union all
select '2008-01-02', 'ABC', 35,53 union all
select '2008-01-03', 'ABC', 38,62 union all
select '2008-01-05', 'ABC', 59,95 union all
select '2008-01-02', 'XYZ', 42,21 union all
select '2008-01-04', 'XYZ', 9,7 union all
select '2008-01-05', 'XYZ', 89,45 union all
select '2008-01-01', 'HHH', 70,52 union all
select '2008-01-03', 'HHH', 3,83 union all
select '2008-01-05', 'HHH', 26,77
where
1) date is always up to the day (no time variation)
2) date and category can be considered a composite unique key
Given a date range (let's say, from 2008-01-01 to 2008-01-05) I need to get the below:
date abc_value1 abc_value2 xyz_value1 xyz_value2 hhh_value1 hhh_value2
---------- ----------- ----------- ----------- ----------- ----------- -----------
01/01/2008 11 12 NULL NULL 70 52
01/02/2008 35 53 42 21 NULL NULL
01/03/2008 38 62 NULL NULL 3 83
01/04/2008 NULL NULL 9 7 NULL NULL
01/05/2008 59 95 89 45 26 77
Ideally, the results include
- every day in the date range (even if there is no corresponding data for that date)
- the columns values to be dependent on the categories found within the date range
I came up with this
-- to fulfill requirement "every day in the date range"
declare @dt table (d smalldatetime)
insert into @dt
select '2008-01-01' union all
select '2008-01-02' union all
select '2008-01-03' union all
select '2008-01-04' union all
select '2008-01-05'
-- to fulfill display all the categories (manually determined)
select convert(varchar(10),d,101) as date,
abc.value1 as abc_value1, abc.value2 as abc_value2,
xyz.value1 as xyz_value1, xyz.value2 as xyz_value2,
hhh.value1 as hhh_value1, hhh.value2 as hhh_value2
from @dt dt
left join @log abc on dt.d = abc.date and abc.category = 'ABC'
left join @log xyz on dt.d = xyz.date and xyz.category = 'XYZ'
left join @log hhh on dt.d = hhh.date and hhh.category = 'HHH'
just for the purpose of generating the end result example, but in a real life situation, both the date range and the categories that may fall within that date range... are dynamic. To make my head spin even more, I also suspect the issue of value2 AND value3 being pulled is making this one complicated statement.
Any ideas? Thoughts? Suggestions?