I am struggling on this issue and was hoping if anyone out there can
help me.
Here is the setup: I have a table with the following data:
TableName: TranDetail
MerchID ItemName Price
------------------
101 A 5
101 B 3.5
101 C 0
102 B 7.6
102 C 4
102 E 65
102 G 4
103 K 35
Table Design
MerchID int
ItemName varchar(50)
Price float
What I would like is a report that looks like this
MerchID A B C E G K
101 5 3.5 0
102 7.6 4 65 4
103 35
This report can change on every run depending on data in table
TranDetail. The column name in report depends on ItemName in table
TranDetail. As seen in the above report, there is no data for Items D,
F, H, I, J and hence they do not show up in the report.
What I need: Code for a stored procedure that can get me this data.
Sorry if this question had already been answered previously. I was unable search the forum on this topic. How will I merge these and then configure the first row as Column names (As this helps to map to the destination column names automatically)
I tried with the following and result is coming for one month i.e. JUL but not with the second Month i.e Jun
SELECT 'Jul1' AS MON, [BNQ], [FNB], [RS] FROM (SELECT REVENUECODE, SUM(ROUND(((Jul/31)*30),0)) AS JUL FROM RM_USERBUDGETTBL WHERE USERNAME='rahul' AND FY=2015 GROUP BY REVENUECODE, USERNAME ) AS SourceTable PIVOT (SUM(JUL) FOR REVENUECODE IN ([BNQ], [FNB], [RS])) AS PivotTable
We need to insert data/rows from a SQL Server 2014 database into MS Access database. The problem is, there are so many columns (100+) in the table and there are so many insert transactions of this kind (from different tables) that it is not very easy to write the code in VB.NET that lists all column names.
Both the Access and SQL Server tables have the same number of columns and the equivalent data types, so inserting is not really the problem. It's just that is there a way to do an insert statement in T-SQL that does not name all the columns?
I was wondering if anyone has an idea of how we could find the table names and column names of the tables in our Sql server database at runtime/dynamically given our connection string? Please let me know.
I have questions and answers from one table, I need to select questions as column names and answers column values as the results for the questions column.
Im trying to get the column names from a database and display them in textboxes. someone has already helped me by tellnig me that i need to use the FillSchema command. Which works just fine and I can see only the colum names in a datagrid when i bind it to that.
The problem is that I do not know how to extract the name of a column and put it in to a textbox ?
Hi,How do I display the column names from my Sql server table?In asp3 the recordset allowed thisforeach key in rs ColumnName = key.name ColumnValue = key.valuenextHow do I do this in .Net?I want to use a DataReader so I can read through each record and only display the ones I want.TABLE_ONEColumn_OneColumn_TwoColumn_Threethanks,
At my work, we are upgrading a number of MS Access 2000 databases to SQL Server 2000. Many of the columns have names that do not follow the rules for Identifiers. For example there are columns with a numeric names 1,2,3,4,5,6,7,8,9. There are also columns with a hyphen in the name and columns that begin with a number and not an underscore or an alpha character. Plus there are columns with names like first, last, position, etc. (There was also columns with reserved words used as the names. I have changed those column names in the databases that I have already converted) Will leaving these names alone create a problem? The database serves as the backend to ASP pages.
Hello,I'm still extremely novice to SQL and I've tried googling how to produce this result but I've been encountering a lot dynamic sql commands which isn't exactly what I want. If this is an ignorant question I do apologize but heres my scenario: I have a table with columns of the day, for instance: Monday_hasData (bit)Monday_DataAnd what I want to do is essentially pass in "Monday" as a parameter and rather than doing an If statement on each row, I would like to just like to do something like assign @dayCheck + "_hasData" to a variable and then use @dayCheck as part of my query. Is this possible or am I going to have to have 7 cases? Thank you for any input,Chance
Hi all, By using below query i can get no of tables having the give column name in a particular database, SELECT COUNT(*) AS CounterFROM syscolumnsWHERE (name = 'empno') but i want to know the table names too? any one please suggest me how to find table names too...... In other words i know the particular column name and right now i want to know the table names in which this column name exists.
Hi, I have a table in which out of 20 columns, there will be data only in a few columns. So, I need to find those column names which have data in them. I was able to do it using the information_schema and using a cursor to loop through all the column names and find out which columns have data in them. But i need to know if there is a more efficient way of doing this without using the cursor. Can somebody please let me know how this can be done without using a cursor? Thank you.
Can anyone help me with a SQL statement that will list all the column names in a table please ?
I just want to list out the column name so that i can develop asp/vb more effectively than having to use SQLEM and Design table to see the field names.:confused:
Hi is possible to create dynamic column name example Declare @StartDate as dateTime Select @StartDate = '2004-06-05'
select SUM(Case When table1_date BETWEEN dateadd(day,-6,@StartDate) and @StartDate then 1 else 0 end)AS [dateadd(day,-6,@StartDate)], SUM(Case When table1_date BETWEEN dateadd(day,-13,@StartDate) and dateadd(day,-7,@StartDate) then 1 else 0 end)AS [dateadd(day,-13,@StartDate)] from table1
Is there a way that you could get the column names for each table in a database using 1 query? something like: tbl colname t1 catID t1 catName t2 prodID t2 prodDesc t3 cartID ... ...
I know it would be long, but I would just be searching through the saved output for specific names.
I was wondering how would I get the column names from a table? Not the results just a listing of column names. What is the command to get this information? I am using MS Access, but I posted here because MS SQL is the closest thing on this forum and the syntax is usually similar.
Morning guys, I have a project where Iam suppose to script all columns in every database/table/columns on the network. I figured the best way to do this would be using sql-dmo (Sql Server 2000)
Does anyone have any suggestions on getting started with this project?
I am writing a stored procedure in which I have a query that selects the Headings of Columns from another table... I want to then create a loop that will contain a variable with the value of the column heading and then set the column to a value of NULL...
Is there any way to accomplish this???
I thought about placing these values into a temp table...
Create Table #UpdateRejDoc ( Abbreviation varchar(20) ) Insert into #UpdateRejDoc Select Abbreviation From tbl_Titles left JOIN tbl_TitleRouting on tbl_Titles.Title_ID = tbl_TitleRouting.Title_ID Where tbl_TitleRouting.Application_ID = @Application_ID While Exists (Select Abbreviation from #UpdateRejDoc) Begin
Set @QueryX = 'Update DBLandfillUser.tbl_ObjectApprovals' + '@AppName + Set @FieldName = null Where object_id =' + Cast(@object_id as VarChar(20)) End
Hi All, Heres my problem... If I create a table T1 with a couple of columns; MyPKCol, ColA I then create a view V1 as "Select * From T1" At this stage, if I run "Select * From V1" the result will, as expected, include following columns: MyPKCol,ColA
If I now add another column, ColB, to T1 and then again run "Select * From V1", I still get the result with only 2 columns; MyPKCol,ColA
In order for V1 to return MyPKCol,ColA,ColB I have to drop V1 and then recreate it again.
Can someone explain why this is and how I can clear this type of cache? I've tried the following commands, but it didn't work: DBCC FREEPROCCACHE; DBCC FREEsystemCACHE( 'ALL' ); DBCC DROPCLEANBUFFERS;
There has to be an easier way than having to recreate SPs, Function and Views just because you make a change to a table.
I hope I made sence Thank you in advance for any help.
I have a SQL table with 5000 rows in it. Some of the other SQL columns have different values but if I wanted to swap say 500 of the rows would it be something like
UPDATE Coupins SET Name='test1,test2,test3' WHERE Name='test4,test5,test6';
I need basically to swap the Name value of these without affecting any other values.
I'm trying to perform an insert on a table from another table, but the fieldnames are different. The data is similar, but there are less fields as well. Does anyone know how to accomplish this? All the material I've read on the Internet so far pertaining to Insert statements, has all the column names matching up.
Got my first real job to do in SSIS and it's not really much fun. Here goes. I have excel files from various vendors, none of which are in the same format. Now I'm less concerned with the names, I have those in variables so that's not a big deal. My issue is around the column names. Let's say we have file a, b and c. In file a the first name field is called [first name], in file b it's first_name and in file c it's firstname. What I want to do is create 1 package that can handle these files regardless of how first name is spelled. I was thinking I could open the file and somehow search for a column name that contained the word "first%" and then map the column name to a variable. I'm sort of stuck though on how to actually do that. Help. And please don't tell me to tell the vendors to standardize. That's the end goal but it's like herding cats.
I have set up a query where I am using a cursor to pass result from onequery to use as select parameters in another query. The problem I amhaving is that when I pass the results into the second query I get thecolumn headers?How do you suppress the column headers from showing in the query? Isee how you select options - print headers.ThanksJason
Hi,I am learning Ms Sql and I found that a lot of the tables I am finding( in different tutorials ) are using special names for their columnnames such like au_username, au_salery ...Is the any naming convention for column names, or just at all is thereany reason for naming the table columns in specific way.Thanks in advance.
