Trying to optimise the below query, I believe it's do with the estimated rows on the unpivot using Supratimes this seems to be the only sticking point.The query below is an example replicating what I'm trying to do in live, it takes around 2 seconds to run on my pc.
Create --drop --alter
Table #Actuals
(
Period1 FLOAT
, Period2 FLOAT
, Period3 FLOAT
, Period4 FLOAT
[code]....
Hi all,
I have more than 1 set of columns I'm trying to normalize, can an unpivot transform handle 2 unpivots around DIFFERENT keys in the same transform?
If not, would the suggestion be unpivot into temp tables for whatever needs normalizing, than run an update query to generate the row that is desired?
Thanks in advance.
Mark
I am not sure if this is the best forum but i could not find anything perfect so lets just try...
Problem:
I have a GUI that has a bunch of data. users can insert or edit data. once they hit save i have a method that goes row by row and then queries the database to see if the row exists in the database. so that is 1 roundtrip. then if it is not, the code spits out an insert statement otherwise an update statement. so that is 2nd roundtrip. This is the best i have come up with but i feel its too slow.
In some other applications i have datagridviews that allow users to insert/edit data and the save is done by using the .net api tableadapter.update(datatable). I don't know what this api is doing under it but it seems like this is much faster. Unfortunately i cannot go this route. Does anyone have any suggestions on how to optimize the problem i have described in the paragraph above?
Mucho Gracias !
Hello Everybody,
I have a small tricky problem here...need help of all you experts.
Let me explain in detail. I have three tables
1. Emp Table: Columns-> EMPID and DeptID
2. Dept Table: Columns-> DeptName and DeptID
3. Team table : Columns -> Date, EmpID1, EmpID2, DeptNo.
There is a stored procedure which runs every day, and for "EVERY" deptID that exists in the dept table, selects two employee from emp table and puts them in the team table. Now assuming that there are several thousands of departments in the dept table, the amount of data entered in Team table is tremendous every day.
If I continue to run the stored proc for 1 month, the team table will have lots of rows in it and I have to retain all the records.
The real problem is when I want to retrive data for a employee(empid1 or empid2) from Team table and view the related details like date, deptno and empid1 or empid2 from emp table.
HOw do we optimise the data retrieval and storage for the table Team. I cannot use partitions as I have SQL server 2005 standard edition.
Please help me to optimize the query and data retrieval time from Team table.
Thanks,
Ganesh
hello friends
my one insert code lines is below :) what does int32 mean ? AND WHAT IS DIFFERENT BETWEEN ONE CODE LINES AND SECOND CODE LINES :)Dim conn As New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
Dim cmd As New SqlCommand("Insert into table1 (UserId) VALUES (@UserId)", conn)
'you should use sproc instead
cmd.Parameters.AddWithValue("@UserId", textbox1.text)
'your value
Try
conn.Open()Dim rows As Int32 = cmd.ExecuteNonQuery()
conn.Close()Trace.Write(String.Format("You have {0} rows inserted successfully!", rows.ToString()))
Catch sex As SqlExceptionThrow sex
Finally
If conn.State <> Data.ConnectionState.Closed Then
conn.Close()
End If
End Try
MY SECOND INSERT CODE LINES IS BELOWDim SglDataSource2, yeni As New SqlDataSource()
SglDataSource2.ConnectionString = ConfigurationManager.ConnectionStrings("ConnectionString").ToString
SglDataSource2.InsertCommandType = SqlDataSourceCommandType.Text
SglDataSource2.InsertCommand = "INSERT INTO urunlistesi2 (kategori1) VALUES (@kategori1)"
SglDataSource2.InsertParameters.Add("kategori1", kategoril1.Text)Dim rowsaffected As Integer = 0
Try
rowsaffected = SglDataSource2.Insert()Catch ex As Exception
Server.Transfer("yardim.aspx")
Finally
SglDataSource2 = Nothing
End Try
If rowsaffected <> 1 ThenServer.Transfer("yardim.aspx")
ElseServer.Transfer("urunsat.aspx")
End If
cheers
When someone clicks my form
i use this code to enter data in Server:
private void Submit_Click(object sender, System.EventArgs e)
{
SqlConnection con = null;
SqlCommand cmd = null;
SqlDataReader rd = null;
try
{
con = new SqlConnection("server=localhost; uid=sa; pwd=123; database=TaskManagement");
cmd = new SqlCommand("INSERT INTO TASK_TYPE (name) VALUES ("+name.Text+")", con);
con.Open();
rd = cmd.ExecuteQuery();
}
catch (Exception e)
{
Response.Write("<p><font color="red">Error: ");
Response.Write(e.Message);
Response.Write("</font></p>");
}
finally
{
if(rd != null)
rd.Close();
if(con != null)
con.Close();
}
}
is that right? I am new in c# and i try to make it work!
Thanks in advance!
Hi I am trying to inset data to my sql 2005 database using a webform.. the code I have is
3 <asp:SqlDataSource ID="SqlDataSource1" runat="server" 4 5 ConnectionString="<%$ ConnectionStrings:SQL2005440975 %>" 6 7 InsertCommand="INSERT INTO [dbo.asp.net_Addresstbl] ([Salutation], [fname], [sname], [Daydb], [Monthdb], [Yeardb], [txtOrg], [txtLine1], [txtLine2], [txtLine3], [txtTown], [txtPostcode], [UserID]) 8 9 VALUES (@Salutation, @fname, @sname, @Daydb, @Monthdb, @Yeardb, @txtOrg, @txtLine1, @txtLine2, @txtLine3, @txtTown, @txtPostcode, @UserID)" 10 11 <InsertParameters>12 <asp:FormParameter FormField="Salutation" Name="Salutation" Type="String" />13 <asp:FormParameter FormField="fname" Name="fname" Type="String" />14 <asp:FormParameter FormField="sname" Name="sname" Type="String" />15 <asp:FormParameter FormField="Daydb" Name="Daydb" Type="Decimal" />16 <asp:FormParameter FormField="Monthdb" Name="Monthdb" Type="String" />17 <asp:FormParameter FormField="Yeardb" Name="Yeardb" Type="Decimal" />18 <asp:FormParameter FormField="txtOrg"Name="txtOrg" Type="String" />19 <asp:FormParameter FormField="txtLine1" Name="txtLine1" Type="String" />20 <asp:FormParameter FormField="txtLine2" Name="txtLine2" Type="String" />21 <asp:FormParameter FormField="txtLine3" Name="txtLine3" Type="String" />22 <asp:FormParameter FormField="txtTown" Name="txtTown" Type="String" />23 <asp:FormParameter FormField="txtPostcode"Name="txtPostcode" Type="String" />24 <asp:FormParameter FormField="UserID" Name="UserID" Type="Object" />25 </InsertParameters>26 </asp:SqlDataSource>27 28 <asp:DropDownList ID="Salutation" runat="server" ValidationGroup="Address">29 <asp:ListItem>Choose One</asp:ListItem>30 <asp:ListItem>Mr.</asp:ListItem>31 <asp:ListItem>Mrs.</asp:ListItem>32 <asp:ListItem>Ms.</asp:ListItem>33 <asp:ListItem>Miss.</asp:ListItem>34 <asp:ListItem>Rev.</asp:ListItem>35 <asp:ListItem>Doc.</asp:ListItem>36 <asp:ListItem>Other.</asp:ListItem>37 </asp:DropDownList>38 39 <asp:TextBox ID="fname" runat="server" CausesValidation="True"></asp:TextBox>40 41 <asp:TextBox ID="sname" runat="server" CausesValidation="True"></asp:TextBox>42 43 <asp:DropDownList ID="Daydb" runat="server" OnSelectedIndexChanged="DropDownList1_SelectedIndexChanged">44 <asp:ListItem>Day</asp:ListItem>45 <asp:ListItem>01</asp:ListItem>46 <asp:ListItem>02</asp:ListItem>47 <asp:ListItem>03</asp:ListItem>48 <asp:ListItem>04</asp:ListItem>49 <asp:ListItem>05</asp:ListItem>50 <asp:ListItem>06</asp:ListItem>51 <asp:ListItem>07</asp:ListItem>52 <asp:ListItem>08</asp:ListItem>53 <asp:ListItem>09</asp:ListItem>54 <asp:ListItem>10</asp:ListItem>55 <asp:ListItem>11</asp:ListItem>56 <asp:ListItem>12</asp:ListItem>57 <asp:ListItem>13</asp:ListItem>58 <asp:ListItem>14</asp:ListItem>59 <asp:ListItem>15</asp:ListItem>60 <asp:ListItem>16</asp:ListItem>61 <asp:ListItem>17</asp:ListItem>62 <asp:ListItem>18</asp:ListItem>63 <asp:ListItem>19</asp:ListItem>64 <asp:ListItem>20</asp:ListItem>65 <asp:ListItem>21</asp:ListItem>66 <asp:ListItem>22</asp:ListItem>67 <asp:ListItem>23</asp:ListItem>68 <asp:ListItem>24</asp:ListItem>69 <asp:ListItem>25</asp:ListItem>70 <asp:ListItem>26</asp:ListItem>71 <asp:ListItem>27</asp:ListItem>72 <asp:ListItem>28</asp:ListItem>73 <asp:ListItem>29</asp:ListItem>74 <asp:ListItem>30</asp:ListItem>75 <asp:ListItem>31</asp:ListItem>76 </asp:DropDownList>77 78 <asp:DropDownList ID="Monthdb" runat="server" style="text-align: left">79 <asp:ListItem>Month</asp:ListItem>80 <asp:ListItem>January</asp:ListItem>81 <asp:ListItem>February</asp:ListItem>82 <asp:ListItem>March</asp:ListItem>83 <asp:ListItem>April</asp:ListItem>84 <asp:ListItem>May</asp:ListItem>85 <asp:ListItem>June</asp:ListItem>86 <asp:ListItem>July</asp:ListItem>87 <asp:ListItem>August</asp:ListItem>88 <asp:ListItem>September</asp:ListItem>89 <asp:ListItem>October</asp:ListItem>90 <asp:ListItem>November</asp:ListItem>91 <asp:ListItem Value="12">December</asp:ListItem>92 </asp:DropDownList>93 94 <asp:DropDownList ID="Yeardb" runat="server" style="text-align: left" 95 OnSelectedIndexChanged="Year_SelectedIndexChanged" 96 DataSourceID="YearDataSource" DataTextField="Year" DataValueField="Year">97 <asp:ListItem Selected="True">Choose Year..</asp:ListItem>98 </asp:DropDownList>99 <asp:AccessDataSource ID="YearDataSource" runat="server" 100 DataFile="~/App_Data/year.mdb" SelectCommand="SELECT [Year] FROM [Year]">101 </asp:AccessDataSource>102 103 <asp:TextBox ID="txtFind" runat="server" CausesValidation="True" ValidationGroup="address"></asp:TextBox>104 105 <asp:Button ID="btnFind" runat="server" Text="Find" 106 OnClick="btnFind_Click" ValidationGroup="address" 107 OnClientClick="lstProperties" />108 109 110 <asp:ListBox ID="lstProperties" runat="server" AutoPostBack="True" 111 OnSelectedIndexChanged="lstProperties_SelectedIndexChanged" Visible="False" 112 Width="200px"></asp:ListBox>113 114 <asp:TextBox ID="txtOrg" runat="server" OnTextChanged="txtOrg_TextChanged" 115 ReadOnly="True" ValidationGroup="address2" Visible="False"></asp:TextBox>116 117 <asp:TextBox ID="txtLine1" runat="server" ReadOnly="True" Visible="False"></asp:TextBox>118 119 <asp:TextBox ID="txtLine2" runat="server" ReadOnly="True" Visible="False"></asp:TextBox>120 121 <asp:TextBox ID="txtLine3" runat="server" ReadOnly="True" Visible="False"></asp:TextBox>122 123 <asp:TextBox ID="txtTown" runat="server" ReadOnly="True" Visible="False"></asp:TextBox>124 125 <asp:TextBox ID="txtPostcode" runat="server" ReadOnly="True" Visible="False"></asp:TextBox>126 127 <asp:Label ID="lblAddress" runat="server" CssClass="style9"></asp:Label>128 129 <asp:Button ID="submitaddress" runat="server" Text="Add Details" 130 style="text-align: centre" CommandName="Submit" 131 ValidationGroup="address" PostBackUrl="~/MemberPages/account.aspx" />132 133 </asp:Content>
Will this code work? if not can you explain why not and offer an example that will work. In the mean time I am reading up on these insert statements.
Regards
Mal
I am having a users table which contains "Mobile" column as well. I want a query to set the country code value by default into the column name so that the column should be updated with the mobile number along with the default country code.
View 10 Replies View RelatedNot sure what happened to my post, it seems to have disappeared. Here we go again. I have a stored procedure that I would like some feedback on, as I feel it may be inefficient as coded:
@ZUserID varchar(10)
AS
SET NOCOUNT ON
DECLARE @counter int
SET @counter = 0
WHILE @counter < 10
BEGIN
SET @counter = @counter + 1
IF EXISTS(SELECT * FROM tblWork WHERE UserID = @ZUserID And LineNumber = @counter)
BEGIN
UPDATE tblWork SET
TransID = Null,
TransCd = Null,
InvoiceNo = Null,
DatePaid = Null,
Adjustment = Null,
Vendor = Null,
USExchRate = Null
WHERE
UserID = @ZUserID And LineNumber = @counter
END
ELSE
INSERT INTO tblWork
(LineNumber,TransCd,UserID)
VALUES
(@counter,'P',@ZUserID)
END
Hello,
I am creating an ASP.NET web site which inserts data in a SQL database using LINQ.
One table column will hold HTML code. I am using a simple TextBox to input the data.
I would like to create my text in my computer and then copy/paste the HTML code to my TextBox and insert in the database.
What software should I use to create my text? Microsoft Office 2007, ... ? Any suggestion would be great.
Can I do it this way?
Thanks,
Miguel
I use the following query to shred an xml and insert it into a table, but I want to have the whole html structure into the column to be able to present the formatted text in Cognos 8 BI.
WITH xmlnamespaces('http://schemas.microsoft.com/office/infopath/2003/myXSD/2007-01-15T13:29:33' AS my)
INSERT INTO TMP_ATGFORSLAG (AtgForslagDesc)
SELECT
AtgForslagRad.value('(/my:AtgForslagRad/my:AtgForslagDesc)[1]', 'varchar(2000)') AS AtgForslagDesc
FROM dbo.DeklAtgForslagXml
The structure of the source in the xmlfile I want to import is:
<my:AtgForslagDesc>
<html xmlns="http://www.w3.org/1999/xhtml">
<ol>
<li>Text1...</li>
<li>Text2...</li>
<li>Text3...</li>
</ol>
</html>
</my:AtgForslagDesc>
How do I shred not only the text but the whole starting <html> to finishing </html> and insert it into a table I would be very happy.
All:
I have created a stored procedure on SQL server that does an Insert else Update to a table. The SP starts be doing "IF NOT EXISTS" check at the top to determine if it should be an insert or an update.
When i run the stored procedure directly on SQL server (Query Analyzer) it works fine. It updates when I pass in an existing ID#, and does an insert when I pass in a NULL to the ID#.
When i run the exact same logic from my aspx.vb code it keeps inserting the data everytime! I have debugged the code several times and all the parameters are getting passed in as they should be? Can anyone help, or have any ideas what could be happening?
Here is the basic shell of my SP:
CREATE PROCEDURE [dbo].[spHeader_InsertUpdate]
@FID int = null OUTPUT,@FLD1 varchar(50),@FLD2 smalldatetime,@FLD3 smalldatetime,@FLD4 smalldatetime
AS
Declare @rtncode int
IF NOT EXISTS(select * from HeaderTable where FormID=@FID)
Begin begin transaction
--Insert record Insert into HeaderTable (FLD1, FLD2, FLD3, FLD4) Values (@FLD1, @FLD2, @FLD3,@FLD4) SET @FID = SCOPE_IDENTITY(); --Check for error if @@error <> 0 begin rollback transaction select @rtncode = 0 return @rtncode end else begin commit transaction select @rtncode = 1 return @rtncode end endELSE
Begin begin transaction
--Update record Update HeaderTable SET FLD2=@FLD2, FLD3=@FLD3, FLD4=@FLD4 where FormID=@FID;
--Check for error if @@error <> 0 begin rollback transaction select @rtncode = 0 return @rtncode end else begin commit transaction select @rtncode = 2 return @rtncode end
End---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Thanks,
Blue.
Hi, all,
Could some body give me little help on this? The UNPIVOT is new to sql05, and I did not find many useful helps out there.
My understanding is that it will normalize the data in column name (the UNPIVOT FOR part). As my sample shows here, I want to get that from one of the row.
Here is the sample, and of cause the Select PIVOT part is not working.
Also, if UNPIVOT is not the right way to go after it, what is the right way?
Thanks!
USE tempdb;
GO
If Object_ID('#pvtSales') IS NOT NULL
Drop table dbo.#pvtSales;
GO
Create Table #pvtSales (Prod_ID varchar(10), Prod_desc varchar(10), Col3 varchar(12), Col4 varchar(10), Col5 varchar(12), Col6 varchar(10), Col7 varchar(12), Col8 varchar(10))
INSERT INTO #pvtSales
Select '111', 'my goody', '1', '1.5', '0', '1.5', '1', '1.3' UNION ALL
Select '222', 'my stuff', '0', '0.5', '1', '0.5', '1', '1.0' UNION ALL
Select NULL, NUll, '2007-06-03', NULL, '2007-06-10', NULL, '2007-06-17', NULL UNION ALL
Select '333', 'my goody3', '1', '2', '0', '2', '1', '2'
Select Prod_ID, Prod_desc, Col3, Col4
FROM #pvtSales
UNPIVOT (Col4 for Col3 in (['06/03/2007'],['06/10/2007'],['06/17/2007']) AS U
The result I am looking for:
Prod_ID/Prod_desc/Sale/Cost/SaleDate
111/my goody/1/1.5/2007-06-03
111/my goody/0/1.5/2007-06-10
111/my goody/1/1.3/2007-06-17
222/my stuff/0/0.5/2007-06-03
...
Hi there,
I have a table with the following fields:
ID, StartDate, Day1, Day2, Day3. This table is filled with hours.
I want to unpivot this table to get the following:
ID, Date (=Start date), hours
ID, Date + 1 (=Start date + 1), hours
ID, Date + 2 (=Start date + 2), hours
The trouble I have concerns the dates. Please help.
HI, I have a source that looks like this:
Col1 Code1 Amt1 Col<n>.....Code2 Amt2....
ABC XY 10 FR 345
What I would like to have is this:
Col1 Code Amt Col<n> ...
ABC XY 10
ABC FR 345
I know I could achieve this by using SQL and UNION ALL:
SELECT Col1, Code1 as Code, Amt1 as Amt, Col<n>...
FROM <mySource>
UNION ALL
SELECT Col1, Code2 as Code, Amt2 as Amt, Col<n>...
FROM <mySource>
But I was wondering if the built-in unpivot transform could do the job here.
Thank you
Ccote
Hi,
I have data stored in a format like this...
RespondantID, Q1, Q2, Q3 ...
1 - Anonymous
1
1
1
1
1
1
1
1
1
1
1
1
1
2 - Anonymous
2
2
2
2
2
2
2
2
2
2
2
2
2
But want to convert it to something like...
SurveyID, RespondantID, QuestionID, Answer
1, 1 - Anonymous, Q1, 1
1, 1 - Anonymous, Q2, 1
...
1, 2 - Anonymous, Q1, 2
1, 3 - Anonymous, Q2, 2
...
Please can someone help.
Thanks!
Hi there,
I'm trying to unpivot a table in SSIS: The pivoted table basically looks like
ID DATE1 TEXT1 DATE2 TEXT2
----------------------------------
ID1 D1 T1 D2 T2
...
The unpivoted result looks like
ID DATE TEXT
-------------------
ID1 D1 T1
ID1 D2 T2How do I get these results in SSIS?
hi need help UNPIVOT table
from this
to the example below
id
fname
val
day1
day11
day111
day2
day22
day222
day3
day33
day333
day4
day44
day444
day5
day55
day555
111
aaaa
2
1
2
3
7
8
9
10
11
12
13
14
15
16
17
18
111
aaaa
1
a
a
a
b
b
b
c
c
c
d
d
d
e
e
e
222
bbbb
2
1
2
3
7
8
9
10
11
12
13
14
15
16
17
18
222
bbbb
1
a
a
a
b
b
b
c
c
c
d
d
d
e
e
e
333
cccc
2
333
cccc
1
444
dddd
2
444
dddd
1
*
555
EEE
2
-
-
-
-
555
EEE
1
*
*
*
i need to do this how
id
fname
val
fld1
fld2
fld3
day_name
111
aaaa
2
1
2
3
1
111
aaaa
1
a
a
a
1
111
aaaa
2
7
8
9
2
111
aaaa
1
b
b
b
2
111
aaaa
2
10
11
12
3
111
aaaa
1
c
c
c
3
111
aaaa
2
13
14
15
4
111
aaaa
1
d
d
d
4
111
aaaa
2
16
17
18
5
111
aaaa
1
e
e
e
5
222
bbb
2
1
2
3
1
222
bbb
1
a
a
a
1
222
bbb
2
7
8
9
2
222
bbb
1
b
b
b
2
222
bbb
2
10
11
12
3
222
bbb
1
c
c
c
3
222
bbb
2
13
14
15
4
222
bbb
1
d
d
d
4
222
bbb
2
16
17
18
5
222
bb
1
e
e
e
5
tnx for the help
Hi,
my Source-Files are not normalized, so I want some Columns unpivot in Rows. For example my base Table looks something like that
ID ... CustomerNo1 ... CustomerName1 ... CustomerPhone1 ... CustomerNo2 ... CustomerName2 ... CustomerPhone2 ...
After Unpivoting I would like to have a structure like that, where all Attributevalues keep the same, but the different Customers in its own Row
ID ... CustomerNo ... CustomerName ... CustomerPhone
So I take the Unpivot-Transformation Task and configure it with this Values:
Input Column ....... Destination Column ........ Pivot Key Value
CustomerNo1 ....... CustomerNo ................... Customer1
CustomerName1 ... CustomerName .............. Customer1
CustomerPhone1 .. CustomerPhone .............. Customer1
CustomerNo2 ....... CustomerNo ................... Customer2
CustomerName2 ... CustomerName .............. Customer2
CustomerPhone2 .. CustomerPhone ............. Customer2
My Data Flow works fine and everything is going without any errors, but my Destination is without the 3rd Attribute (CustomerPhone). Whats wrong in my configuration?
Thanks ...
Jonas
Dear All,
We are trying to unpivot the columns into rows but the colunms are changing dynamically, want to know how to set the dynamic value or variable in unpivot query, query is mentioned below:
declare @aw Varchar(100)
set @aw = '1990,1991'
SELECT [name], [year], [data]
FROM (SELECT * FROM t1 where [name] != 'name') p
UNPIVOT
([data] FOR [year] IN
(@aw)
)AS unpvt
The above query while executing geting error message "Incorrect syntax near '@aw'"
please can anybody solve this issue.
Thanks,
Syed
Hi,
I need to unpivot an excel worksheet with the following format
Artcode
Model
Qtd1
Price1
Qtd2
Price2
Qtd3
Price3
Article1
Model1
1
10
2
11
1
12
Article2
Model2
3
15
3
12
2
14
Article3
Model3
2
12
2
20
1
15
If I had only one destination column, for ex: Qty, I had no problem, but I need to have 2 destination columns, Qty and Price. Is I set 2 different destinationcolumns I get the following error:
Unpivot [2514]: PivotKeyValue is not valid. In an UnPivot transform with more than one unpivoted DestinationColumn, the set of PivotKeyValues per destination must match exactly.
Is there a way to do it?
Hi
When using unpivot transformation, what exactly this error denote
"Incorrect UnPivot metadata. In an UnPivot transform, all input columns with a PivotKeyValue that is set, and are pointing to the same DestinationColumn, must have metadata that exactly matches "
data on which i was trying unpivoting is -
Name
Pd1
Pd2
Pd3
Pd4
Utsav
111
211
311
411
Verma
122
222
322
422
Nucleus
133
233
333
433
Noida
144
244
344
444
HI friends i need the pivot an unpivot example
package please send the link with atleast pictures steps
I totally give up.
I need to import an csv file with system monitor performance counters into a sql server 2005 database
example csv format:
"(PDH-CSV 4.0) (W. Europe Daylight Time)(-120)","Server1Memory\% Commited Bytes In Use", )","Server2Memory\%Commited Bytes In Use"
"07/18/2006 10:08:57.295","24.701095057336488","30.701095077776488"
and I want it to transform it into:
Time ServerName Memory\% Commited Bytes In Use
07/18/2006 10:08:57.295 Server1 24.701095057336488
07/18/2006 10:08:57.295 Server2 30.701095077776488
I have two problems:
I need to trim the servername from the column values
I need to insert an extra column ServerName and put those server names in it
I tried Unpivot and Fuzzy grouping in SSIS but it's al too fuzzy for me...
Any pointers someone?
Many thanks!
Regards,
Mop
Hi All,
We are trying to unpivot the columns into rows but the colunms are changing dynamically, want to know how to set the dynamic value or variable in unpivot query, query is mentioned below:
declare @aw Varchar(100)
set @aw = '1990,1991'
SELECT [name], [year], [data]
FROM (SELECT * FROM t1 where [name] != 'name') p
UNPIVOT
([data] FOR [year] IN
(@aw)
)AS unpvt
The above query while executing geting error message "Incorrect syntax near '@aw'"
please can anybody solve this issue.
Thanks,
Syed
I am using the unpivot transformation, but I can't figure out how to use an expression in the Pivot Key Value.
The denormalized table I want to unpivot has columns like Sunday_Qty, Monday_Qty, Tuesday_Qty, etc. Just before the unpivot component, I inserted a derived column component that adds fields like DateSun, DateMon, DateTue, etc. that resolves to values like 01/07/2007, 01/08/2007, etc.
So for the various rows in the Unpivot Transformation Editor, I entered DateSun, DateMon, DateTue, etc. for the Pivot Key Value, and "EntryDate" for the pivot key value column name.
The data pipeline gets unpivoted correctly, but the rows have the literal values "DateSun", "DateMon", etc. in the EntryDate column.
How do I tell SSIS to use the DateSun column instead of the string "DateSun"?
-Larry
Hi,
I have a 2 outputs from 2 individual Unpivot transformation, now i want to compare the resultant of these two and find the odd / unmatched rows, could anyone please tell me how to achieve this?
Hi all,
I am running into a problem which seems to indicate SQL Server 2005 is pretty aggressive with its implicit conversion which makes UNPIVOT apparently unusable for my current task of creating attribute/value pairs of strings for each of the records unpivoted around the primary key.
WITH cte as
(
SELECT 1 as Code, '2' as Status, 'Some Guy' as CreatedBy, Convert(varchar(19), getdate(), 120) as CreatedDate
)
SELECT
[Code]
,[Attribute]
,[Value]
FROM
cte
UNPIVOT([Attribute] FOR [Value] IN ([Status],[CreatedBy],[CreatedDate])) as U;
Msg 8167, Level 16, State 1, Line 1
The type of column "CreatedBy" conflicts with the type of other columns specified in the UNPIVOT list.
The same result shows up if I use an inner SELECT instead of the CTE:
SELECT
[Code]
,[Attribute]
,[Value]
FROM
(
SELECT 1 as Code, '2' as Status, 'Some Guy' as CreatedBy, Convert(varchar(19), getdate(), 120) as CreatedDate
) cte
UNPIVOT([Attribute] FOR [Value] IN ([Status],[CreatedBy],[CreatedDate])) as U;
It seems to me that some arbitrary decision about what column is processed first is made and the process fails as soon as a column is met which is not of the same datatype. I also think it is interesting that the engine will implicitly cast from varchar to DateTime but not the other way around.
Any thoughts on how I can get around this?
Thanks in advance,
Calvin
I have below table and within same query i need pivot and unpivot.
create table #temp(name1 varchar(10),name2 varchar(10),name3 varchar(10),month date,emp1 int,emp2 int,emp3 int,emp4 int)
insert into #temp values ('a','b','c','1-1-2013',1,2,3,4)
insert into #temp values ('a','b','c','1-2-2013',11,20,30,40)
insert into #temp values ('a','c','c','1-1-2013',22,30,80,40)
insert into #temp values ('a','c','c','1-2-2013',28,34,39,30)
select * from #temp
Now i need output in below format
name1,name2,name3,Emp,jan-13,feb-13
a,b,c,emp1,1,11
a,b,c,emp2,2,20
a,b,c,emp3,3,30
a,b,c,emp4,4,40
a,c,c,emp1,22,28
a,c,c,emp2,30,34
a,c,c,emp3,80,39
a,c,c,emp4,40,30
Hello everyone!
Maybe someone of you folks already stumbled upon this one and can help.
I'm using an Unpivot transform in a dataflow.
I want to unpivot various columns that are all of type dt_str(50).
Some of those (input) columns may contain NULL values in some rows, like this:
col1
col2
col3
col4
BusinessKey
val1
NULL
val3
val4
As I run this package, I'd expect the Unpivot's output looking like this:
BusinessKey
col1
val1
BusinessKey
col2
NULL
BusinessKey
col3
val3
BusinessKey
col4
val4
But Unpivot won't output a Key/Value pair if an input value is NULL.
So in fact the output looks like this (col2 is missing):
BusinessKey
col1
val1
BusinessKey
col3
val3
BusinessKey
col4
val4
The BOL documentation doesn't tell anything about Unpivot behaviour if input values are NULL (or at least I couldn't find it).
Now, is this a known problem?
If so, is there a fix or workaround so that I can tell Unpivot to output NULL values?
Thanks for your help!
Hi, I am trying to use the Unpivot Transformation, and I have the following mapped out, following the example from the SQL Server 2005 BOL Unpivot Transformation:
Input
Destination Column
Subject
ValueNumeric
ValueString
ValueNumeric
Pivot Key Value
RACE
OTHRRACE
GENDER
Column Name
Subject
RACE
OTHRRACE
GENDER
Data Records
99999
1
NULL
1
88888
NULL
Hispanic
2
77777
2
NULL
2
Desired Output
Pivot Key
FALSE
TRUE
Col Name
Subject
Question
ValueNumeric
ValueString
Data Records
99999
RACE
1
NULL
99999
OTHRRACE
NULL
NULL
99999
GENDER
1
NULL
88888
RACE
NULL
NULL
88888
GENDER
2
NULL
88888
OTHRRACE
NULL
Hispanic
77777
RACE
2
NULL
77777
GENDER
2
NULL
77777
OTHRRACE
NULL
NULL
This doesn't work, however, as I have multiple data types. So unless someone knows a better way, I had to split it into two Unpivot Transformations, which I later Sort and Merge together. The two Unpivot Transformations look like this:
Input
Destination Column
Subject
ValueNumeric
ValueNumeric
Pivot Key Value
RACE
GENDER
Column Name
Subject
RACE
GENDER
Data Records
99999
1
1
88888
NULL
2
77777
2
2
Output
Pivot Key
FALSE
TRUE
Col Name
Subject
Question
ValueNumeric
Data Records
99999
RACE
1
99999
GENDER
1
88888
RACE
NULL
88888
GENDER
2
77777
RACE
2
77777
GENDER
2
Input
Destination Column
Subject
ValueString
Pivot Key Value
OTHRRACE
Column Name
Subject
OTHRRACE
Data Records
99999
NULL
88888
Hispanic
77777
NULL
Output
Pivot Key
FALSE
TRUE
Col Name
Subject
Question
ValueString
Data Records
99999
RACE
NULL
99999
OTHRRACE
NULL
99999
GENDER
NULL
88888
RACE
NULL
88888
GENDER
NULL
88888
OTHRRACE
Hispanic
77777
RACE
NULL
77777
GENDER
NULL
77777
OTHRRACE
NULL
The first Unpivot works great, because the data is mandatory. However, the second is full of NULLs so the actual output from the second Unpivot, prior to the merge looks like this:
Actual Output
Subject
Question
ValueString
99999
88888
OTHRRACE
Hispanic
77777
But I expected (and need) this:
Desired Output
Subject
Question
ValueString
99999
OTHRRACE
NULL
88888
OTHRRACE
Hispanic
77777
OTHRRACE
NULL
I hope someone can give me some advice on this!
Regards,
Richard Hein
Hi there,
I have an issue that feels like it requires an Unpivot task, but I'm struggling to visualise the best way to do it. I'd be grateful for any advice on this.
I have this dataset in my pipeline
moduleid
startdate
modenddate
Revenue
MonthsOnModule
800091
05/09/2007
30/12/2007
150
3
800094
05/09/2007
30/11/2007
148
2
800095
05/09/2007
30/12/2007
300
3
Basically, each module has a start date and an end date. Therefore, with a simple datediff, we can get to MonthsOnModule.
Each module attracts an amount of revenue, which need to be allocated equally to each month over which the module runs.
In the example above, Module 800091 lasts 3 months, generates £150 revenue, so in September 2007, it attracts £50, in October 2007 It attracts £50, In November 2007 it attracts £50.
I'm using this package to populate a fact table in an OLAP Data Warehouse. The destination table needs to hold the data like this:
moduleid
Month
value
800091
200709
50
800091
200710
50
800091
200711
50
800094
200709
74
800094
200710
74
800095
200709
100
800095
200710
100
800095
200711
100
Now that looks like a pretty straight unpivot, but in order to unpivot, you have to have columns to unpivot into rows. In this case, I would need one column for each month that the module lasted. Modules can last any number of months. Some last 40 months, and there is no reason why one couldn't last 100 months or more.
I just can't really seem to visualise my approach to this transform. Anyone got any ideas?
Thank you
Hi
I have an Issue. Please find the sample of my data source
StudentID
Fee 1
Fee 1 Currency Type
Fee2
Fee 2 Currency Type
1
10
USD
20
INR
2
45
EUR
20
USD
If I need to transform this data, it should be like below Table.
ID
StudentId
FeeType
FeeAmount
CurrencyType
1
1
Fee 1
10
USD
2
1
Fee 2
20
INR
3
2
Fee 1
45
EUR
4
2
Fee 2
20
USD
I have selected columns Fee 1 and Fee 2 in Unpivot Transformation. I set the "FeeAmount" as Destination Column Name and "FeeType" as Pivot Key Value Column Name. I left the Pivot Key Value as is for all the selected columns. So far no issue that I am able to get the Fee Type and Fee Amount columns for my destination table.
But the issue is how to get the Currency Type for each corresponding coulmns? I was not able to get the currency type for each student. I hope you got my point. Please let me know If have further queries and hel me out to find this.