Database Discussion Boards

Mycroft Holmes4-Nov-15 12:57

4-Nov-15 12:57

My apologies, I was being sarcastic. The point I was trying to make is that Chris had given him the pointers where he could research and learn how to do the job. Whereas you gave him the solution, which does not help him learn.

If it was a difficult question then the example may have been the best way to answer. As it is an extremely basic question the OP needs to do some learning/research/school work rather than cut and paste your answer.

Asking someone to "do the needful" is just plain rude.

Never underestimate the power of human stupidity
RAH

Rows as column using Pivot but with multiple rows and Column

indian14330-Oct-15 3:55

indian143

30-Oct-15 3:55

Hi All,

I am using the following script to transpose a particular column row as columns, but the problem is I am able to get only one row, for example I have StudentNames, Ids, AggregateMarks, and TotalMarks.

If I use the below approach and generate a column for every student then I am able to get only TotalMarks for each student, but I want to get Ids, AggregateMark also under each studentName

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(StudentNames) 
                    from yourtable
                    group by StudentNames, id
                    order by id
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = N'SELECT ' + @cols + N' from 
             (
                select value, ColumnName
                from yourtable
            ) x
            pivot 
            (
                max(value)
                for ColumnName in (' + @cols + N')
            ) p '

exec sp_executesql @query;

Can anybody please help me in this, any code snippet or suggestion or a link would be very very helpful, please thanks in advance.

Thanks,

Abdul Aleem

"There is already enough hatred in the world lets spread love, compassion and affection."

Re: Rows as column using Pivot but with multiple rows and Column

Chris Quinn30-Oct-15 5:43

Chris Quinn

30-Oct-15 5:43

You could have a look at this[^]

=========================================================
I'm an optoholic - my glass is always half full of vodka.
=========================================================

Re: Rows as column using Pivot but with multiple rows and Column

indian14330-Oct-15 5:44

indian143

30-Oct-15 5:44

I have Printed the dynamic query, it is as below. But same thing how to get that ServiceLevel from the below query, it is printing only Service id for each column in a row, but I want Service Level also in another row, can't we do it? Any sort of help would be very very helpful, please help me I am also trying my best. Thanks in advance.

SELECT [Cartilage Regeneration],[Foot and Ankle Care],[Hand, Wrist and Elbow Care],[Hip Care],[Joint Replacement],[Knee Care],
[Orthopedic Trauma Surgery],[Pediatric Orthopedics],[Shoulder Care],[Sports Medicine],[Tendon and Ligament Repair] from 
             (
                select  ServiceLevel, ServiceId, Name
                from [Service]             
            ) x 
            pivot 
            (
                max(ServiceId)
                for Name in ([Cartilage Regeneration],[Foot and Ankle Care],[Hand, Wrist and Elbow Care],[Hip Care],[Joint Replacement],
                [Knee Care],[Orthopedic Trauma Surgery],[Pediatric Orthopedics],[Shoulder Care],[Sports Medicine],[Tendon and Ligament Repair])
            )
             p

Thanks,

Abdul Aleem

"There is already enough hatred in the world lets spread love, compassion and affection."

Re: Rows as column using Pivot but with multiple rows and Column

RNA Team30-Oct-15 19:23

RNA Team

30-Oct-15 19:23

@indian1433, here is a way (The first version is static)

DECLARE @T TABLE(StudentNames VARCHAR(20), Id INT, AggregateMarks INT, TotalMarks INT)
INSERT INTO @T VALUES
	('NAME1',1,300,503),
	('NAME2',2,250,500),
	('NAME3',3,378,504),
	('NAME4',4,490,500),
	('NAME5',5,399,500)

SELECT X.ID,X.TotalMarks,t1.[AggregateMarks],X.[NAME1],X.[NAME2],X.[NAME3],X.[NAME4],X.[NAME5]
FROM @T 
PIVOT 
(
    MAX(AggregateMarks)
    FOR StudentNames
    IN ([NAME1],[NAME2],[NAME3],[NAME4],[NAME5])
) AS X
JOIN @T t1 on t1.Id  = X.Id 
ORDER BY 1

I have done this one to show you how it must go like. The dynamic version follows

CREATE TABLE #T (StudentNames VARCHAR(20), Id INT, AggregateMarks INT, TotalMarks INT)
INSERT INTO #T VALUES
	('NAME1',1,300,503),
	('NAME2',2,250,500),
	('NAME3',3,378,504),
	('NAME4',4,490,500),
	('NAME5',5,399,500)

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)
 
select @cols = STUFF((SELECT ',' + QUOTENAME(StudentNames) 
                    from #T
                    group by StudentNames, id
                    order by id
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = N'SELECT X.ID,X.TotalMarks,t1.AggregateMarks, ' + @cols + N' from #T             
            PIVOT 
            (
                 MAX(AggregateMarks)
                 FOR StudentNames IN (' + @cols + N')
            ) AS X 	
			JOIN #T t1 on t1.Id  = X.Id 		
			ORDER BY 1'

print @query

exec sp_executesql @query

DROP TABLE #T

Hope this helps.

modified 31-Oct-15 2:14am.

Re: Rows as column using Pivot but with multiple rows and Column

sandeepmittal118-Nov-15 0:04

sandeepmittal11

8-Nov-15 0:04

You need to "group by" your query with student name
Refer related post:
Pivot with dynamic columns

My Tech Blog : IT Developer Zone

Re: Rows as column using Pivot but with multiple rows and Column

Richard Deeming9-Nov-15 2:01

Richard Deeming

9-Nov-15 2:01

Linking to an article on your own blog, without posting the relevant details here, is considered spam.

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Re: Rows as column using Pivot but with multiple rows and Column

sandeepmittal119-Nov-15 19:25

sandeepmittal11

9-Nov-15 19:25

Okies... Thanks for the information...Will take care...

My Tech Blog : IT Developer Zone

Error in IF statement in MySQL

Jassim Rahma29-Oct-15 4:31

Jassim Rahma

29-Oct-15 4:31

Hi,

I am wondering why I am getting error when running below simple IF in MySQL?

SQL

IF NOT EXISTS (SELECT employee_attendance_id FROM employee_attendance WHERE employee_number = 10 AND attendance_date_time = '2015-10-11 13:14:16') THEN
BEGIN
    INSERT IGNORE INTO employee_attendance (employee_number, attendance_date_time)  VALUES (10, '2015-10-11 13:14:16');
END;
END IF;

This is the error I am getting:

[SQL] IF NOT EXISTS (SELECT employee_attendance_id FROM employee_attendance WHERE employee_number = 10 AND attendance_date_time = '2015-10-11 13:14:16') THEN
BEGIN
    INSERT IGNORE INTO employee_attendance (employee_number, attendance_date_time)  VALUES (10, '2015-10-11 13:14:16');
[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF NOT EXISTS (SELECT employee_attendance_id FROM employee_attendance WHERE empl' at line 1

Thanks,
Jassim[^]

Technology News @ www.JassimRahma.com

Re: Error in IF statement in MySQL

Eddy Vluggen29-Oct-15 5:08

Eddy Vluggen

29-Oct-15 5:08

..probably because you are showing a query for SQL Server. The error says to look up the correct syntax[^].

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

Re: Error in IF statement in MySQL

Wombaticus29-Oct-15 5:45

Wombaticus

29-Oct-15 5:45

Apart from what Eddy Vluggen said, you might want to look up MySQL's REPLACE[^] and/or INSERT ... ON DUPLICATE KEY UPDATE[^] syntax as an alternative way to achieve your goal.

Get all Tables which have Duplicate values on Columns Name and Description

indian14328-Oct-15 14:09

indian143

28-Oct-15 14:09

Hi All,

We are moving the data from a huge database into another database as we guess we have Duplicate values on Name and Description fields in some tables we want to list our all those Tables which have these columns and duplicates on those columns using some script dynamically.

Is there any way to write that sort of script if some body already did it and can help me, it would be a great help I am also searching any link, suggestion or code snippet would be a great help.

Thanks in advance.

Thanks,

Abdul Aleem

"There is already enough hatred in the world lets spread love, compassion and affection."

Re: Get all Tables which have Duplicate values on Columns Name and Description

Jörgen Andersson28-Oct-15 22:31

Jörgen Andersson

28-Oct-15 22:31

How to do it would depend a lot on the database. You should update your post with that info.

Wrong is evil and must be defeated. - Jeff Ello

Re: Get all Tables which have Duplicate values on Columns Name and Description

indian14329-Oct-15 8:29

indian143

29-Oct-15 8:29

anyways is fine it is just an internal running script but I want to run it on all tables in the selected database on selected server. I though of little bit with dynamic sql but yes that's question too, is there any better approach for it than dynamic sql?

Thanks,

Abdul Aleem

"There is already enough hatred in the world lets spread love, compassion and affection."

Re: Get all Tables which have Duplicate values on Columns Name and Description

RNA Team30-Oct-15 20:13

RNA Team

30-Oct-15 20:13

@indian1433, you can try with sp_MSforeachdb and sp_MSforeachtable .Please check out and let us know if the hint has helped you.

N.B.~ Since you mentioned that it's just for internal purpose, so I have mentioned those two undocumented Stored Procedure. Please don't use it in production since they r undocumented and there is no guarantee that in future MS will not remove them (:

Load data from Excel which has columns in multiple rows

indian14328-Oct-15 4:08

indian143

28-Oct-15 4:08

Hi All,

I have an Excel file in which there are multiple sheets and its columns are spread in multiple rows like a parent has multiple children and those children have their children.

How can I load this data in to a table sheet by sheet, means for each load if it loads from one sheet that's fine but important thing here is columns are spread into multiple rows.

I am also searching, but any link, any suggestion or code snippet is going to be very very helpful. Thanks in advance.

Thanks,

Abdul Aleem

"There is already enough hatred in the world lets spread love, compassion and affection."

Re: Load data from Excel which has columns in multiple rows

Richard MacCutchan28-Oct-15 7:18

Richard MacCutchan

28-Oct-15 7:18

See Working with MS Excel(xls / xlsx) Using MDAC and Oledb[^].

Re: Load data from Excel which has columns in multiple rows

indian14328-Oct-15 8:37

indian143

28-Oct-15 8:37

Sorry I didn't mention it may be but I want to do it SQL Server script. So I am trying in the following way

SELECT * INTO #Servxxx
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
  'Excel 12.0 Xml; HDR=YES; IMEX=1;
   Database=D:\Servxxx.xls',
   [Servxxx First Sheet$]);

It is giving me the following error

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "The Microsoft Access database engine could not find the object 'D:\Servxxx.xls'. Make sure the object exists and that you spell its name and the path name correctly. If 'D:\Servxxx.xls' is not a local object, check your network connection or contact the server administrator.".
Msg 7303, Level 16, State 1, Line 10
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

It is saying file is not there, but file is there and it is erroring out with link server etc.

Thanks,

Abdul Aleem

"There is already enough hatred in the world lets spread love, compassion and affection."

Re: Load data from Excel which has columns in multiple rows

Richard Deeming28-Oct-15 10:05

Richard Deeming

28-Oct-15 10:05

Is D: a local drive, or a mapped network drive?
Does the SQL service account have permission to access the file?

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Re: Load data from Excel which has columns in multiple rows

indian14328-Oct-15 11:02

indian143

28-Oct-15 11:02

Hey Rick, thanks I got it sort out my friend here is the sample one may be somebody has the same issue.

SELECT * INTO #xxxxxxs
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0 Xml; HDR=YES; IMEX=1;
Database=C:\xxxxxxx\Dont Remove ThisFolder\xxxxxxs.xlsx',
'SELECT * FROM [Back xxxxx$]');

I have a question here, do we have any way to copy the existing schema structure into a table variable, along with an identity column?

Thanks,

Abdul Aleem

"There is already enough hatred in the world lets spread love, compassion and affection."

Use column output, to get new column

Karan_TN27-Oct-15 19:54

Karan_TN

27-Oct-15 19:54

I am using Sql server 2008.
I have 3 columns say, UserID,ManagerID,deptCode.
I need the output as UserID,ManagerID,deptCode,ManagersDeptCode

So, the column "ManagerID" need to be used to generate the output of "ManagersDeptCode".

Please help.

Re: Use column output, to get new column

Mycroft Holmes27-Oct-15 20:42

Mycroft Holmes

27-Oct-15 20:42

Join back to your user table using the managerid ot userid to get the managers details

SQL

Select U., M.
From UserTable U
inner join UserTable M on M.userID = U.ManagerID

Never underestimate the power of human stupidity
RAH

select to Cancatenate sub group values under a group

indian14327-Oct-15 3:07

indian143

27-Oct-15 3:07

Hi All,

I have a table A in it I have columns like Id, ParentId, ParentName, Child1Id, Child1Name, Child2Id, Child2Name. ParentId is the parent of Child1Id, and Child1Id is the parent of Chil2Id. Id is the Primary key of this table.

I want to have a select statement which, I have to get ParentIds, ParentNames order by ParentNames, under each Parent, I want to have Child1Ids, Child1Names, order by Child1 Names, Under each Child1s, I want to have its child2Ids and Child2Names.

ParentId, ParentName
Child1Id, Child1Name
Child2Id Child2Name
Child2Id Child2Name
Child2Id Child2Name
Child1Id Chil1Name
Child2Id Child2Name

Is there any way to write a query like this? Please help me, any type of help, a link, a code snippet or a suggestion would be greatly helpful, thanks in advance.

Thanks,

Abdul Aleem

"There is already enough hatred in the world lets spread love, compassion and affection."

Storing Result Of A Select Statement In A Variable

MadDashCoder23-Oct-15 13:13

MadDashCoder

23-Oct-15 13:13

Hi everyone, I have a temporary table as shown below

SQL

tempTable(ID int not null identity(1,1) primary Key, Col1 int, Col2 int, Col3 int)

I am trying to get data of each column, store them in a variable, and manipulate them as I loop through the table using while loop.

The following is what I have tried

SQL

create proc [dbo].[spGetData]
@Col1 int
@Col2 int
@Col3 int
as begin
declare @count int
declare @currentColumn varchar(25)
declare @currentColumnVal varchar(25)
set @count = 1
set @currentColumn = NULL
set @currentColumnVal = NULL
create #tempTable(ID int not null identity(1,1) primary Key, Val1 int, Val2 int, Val3 int)
insert #tempTable(Val1, Val2, Val3) Values(@Col1, @Col2, @Col3)
set @count = 1

while @count < 3
begin 
 set @currentColumn = 'Val' + CAST(@count AS VARCHAR(25))  
 select @currentColumnVal = @currentColumn from #tempTable
 -- Do stuff
 -- Do more Stuff
 set @count = @count + 1
end 

print @currentColumnVal -- Just to see what the value of the last Column is

end

I'm not getting any errors but I'm getting 0 as the return value which is incorrect.

modified 23-Oct-15 20:46pm.

Re: Storing Result Of A Select Statement In A Variable

CHill6025-Oct-15 4:52

CHill60

25-Oct-15 4:52

What were you expecting? There is no final select in this SP. In fact you can't create the SP because there are errors reported.

If I fix the errors ... commas after @Col1 int and @Col2 int and create TABLE #tempTable the Stored Procedure is created and when I run it, it prints "Val2" - which is what I would expect given what you have in the body of the SP.

Are you actually trying to use dynamic sql? Have a look at this CP article - Building Dynamic SQL In a Stored Procedure[^]

You also need to understand how to return values from Stored Procedures - see MSDN article[^]

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.