Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL

Posted on 2010-09-04
30
Medium Priority
?
397 Views
Last Modified: 2012-05-10
Hello,
I have a small SQL Script to get some payroll numbers,
I'd like to have the columns to show seperate for certain codes,
Please see attaced sample (Sheet one is the way it is now, Sheet 2 is the way I'd like to have it)

The script is pretty simple,
Select * from Payroll where ...
Any help is appreciated.
Thanks,
sAMPLE.xlsx
0
Comment
Question by:W.E.B
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 13
  • 10
  • 5
  • +2
30 Comments
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 33604328
You could try dynamic SQL.  Assuming that Code is an int value...
DECLARE @sql varchar(8000)

SET @sql = 'SELECT Payno, Employee, Code, Amount, Info, Entry, CreatedDATE, ChqDate'

SELECT @sql = @sql + 
    ', CASE WHEN Code = ' + CAST(Code AS varchar(10)) + ' THEN Code ELSE NULL END AS Code' + CAST(Code AS varchar(10)) + ', ' +
    'CASE WHEN Code = ' + CAST(Code AS varchar(10)) + ' THEN Amount ELSE 0 END AS Code' + CAST(Code AS varchar(10)) + 'Amount'
FROM Payroll
GROUP BY Code
ORDER BY Code

SET @sql = @sql + ' FROM Payroll ORDER BY Employee, CreatedDATE'

EXEC(@sql)

Open in new window

0
 

Author Comment

by:W.E.B
ID: 33604403
Hello,
I get an error message

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '.'.

Sorry , I made a mistake, I checked my script again, it does have a join,

Select Distinct RegData.RegLink,RegData.eeLink,
RegData.ChqType,RegData.RegCode,RegData.RegValue,RegData.RegAltValue,RegData.AutoNumber,
RegData.CreatedDate,RegData.EndDate,RegData.idUser ,
(Select ChqDate from Register where Register.RegLink = RegData.RegLink) AS [ChqDate]
From RegData
Inner Join eeEmployee on RegData.eeLink = eeEmployee.eeLink
where RegData.createddate between '2010-08-01 12:01:19.000' and '2010-08-31 12:01:19.000'
order by RegData.eeLink

Thanks,
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 33604457
Wass_QA,

When I import your sample file into SQL 2005 and run my batch, I get the exact output you said you wanted.

I suggest that you create a view that uses the SQL statement you posted above, and use that view as the table source in my SQL statement.

Patrick
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

Author Comment

by:W.E.B
ID: 33604514
Hello,
yep, it didn't give me any error,
but nothing shows up?
it just says, Command(s) completed successfully.

Not sure how to view the results.
I use sql 2005

DECLARE @sql varchar(8000)

SET @sql = 'Select Distinct RegData.RegLink,RegData.eeLink,
RegData.ChqType,RegData.RegCode,RegData.RegValue,RegData.RegAltValue,RegData.AutoNumber,
RegData.CreatedDate,RegData.EndDate,RegData.idUser ,
(Select ChqDate from Register where Register.RegLink = RegData.RegLink) AS [ChqDate]'

SELECT @sql = @sql +
', CASE WHEN Code = ' + CAST(RegData.RegCode AS varchar(10)) + ' THEN RegData.RegCode ELSE NULL END AS Code' + CAST(RegData.RegCode AS varchar(10)) + ', ' +
 'CASE WHEN Code = ' + CAST(RegData.RegCode AS varchar(10)) + ' THEN Amount ELSE 0 END AS RegData.RegCode' + CAST(RegData.RegCode AS varchar(10)) + 'Amount'

From RegData
Inner Join eeEmployee on RegData.eeLink = eeEmployee.eeLink
where RegData.createddate between '2010-08-01 12:01:19.000' and '2010-08-31 12:01:19.000'

GROUP BY RegData.RegCode
ORDER BY RegData.RegCode

Thanks,
0
 

Author Comment

by:W.E.B
ID: 33605056
Hello,
Your help is apprciated.
I don't know how to view the results.

Thanks,
0
 

Accepted Solution

by:
chrisghall earned 300 total points
ID: 33606029
Surely this is quite simple to do longhand.....

select Payno, Employee, Code, Amount, Info, Entry, CreatedDATE, ChqDate, case when Code=19 then '19' else NULL end as 'Code19', case when Code=19 then Amount else 0 end as Code19Amount, ....... etc
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 33606171
>>Surely this is quite simple to do longhand.....

It should be, but only as long as the codes are known beforehand :)

If the codes are not know beforehand, then dynamic SQL is the only recourse.
0
 

Author Comment

by:W.E.B
ID: 33606298
Hello,

matthewspatrick is correct, the codes are not known,
thre are over few hundred codes, and they differ between each payroll.

Please advice,
Thanks.
0
 

Author Comment

by:W.E.B
ID: 33606643
Hello,

I tried chrisghall way (for my known Codes), it worked,
how can I add the Sum for the Amounts? (Example Below, I need SUM of 'FedTaxAmount')

Select Distinct RegData.RegLink,RegData.eeLink,
RegData.ChqType,RegData.RegCode,RegData.RegValue,RegData.RegAltValue,RegData.AutoNumber,
RegData.CreatedDate,RegData.EndDate,RegData.idUser ,
(Select ChqDate from Register where Register.RegLink = RegData.RegLink) AS [ChqDate],

CASE When RegData.RegCode = '019' THEN 'FedTax'else Null end as 'FedTax',
CASE When RegData.RegCode = '019' then RegData.RegValue else 0 end as 'FedTaxAmount',
From RegData
left Join eeEmployee on RegData.eeLink = eeEmployee.eeLink
where RegData.createddate between '2010-01-01 12:01:19.000' and '2010-01-31 12:01:19.000' and RegData.eelink = 347
order by RegData.eeLink,ChqDate


Help is appreciated,
Thanks,
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 33607000
OK, try this...


DECLARE @sql varchar(8000)

SET @sql = 'Select Distinct rd.RegLink, rd.eeLink, rd.ChqType, rd.RegCode, rd.RegValue, 
    rd.RegAltValue, rd.AutoNumber, rd.CreatedDate, rd.EndDate, rd.idUser, 
    (Select r.ChqDate from Register r where r.RegLink = rd.RegLink) AS [ChqDate]'

SELECT @sql = @sql + 
    ', CASE WHEN RegCode = ' + RegCode + ' THEN RegCode ELSE NULL END AS Code' + RegCode + ', ' +
    'CASE WHEN RegCode = ' + RegCode + ' THEN rd.RegValue ELSE 0 END AS Code' + RegCode + 'Amount'
FROM RegData
GROUP BY RegCode
ORDER BY RegCode

SET @sql = @sql + ' From RegData r left Join 
    eeEmployee e on rd.eeLink = e.eeLink
    where rd.createddate between ''2010-01-01 12:01:19.000'' and ''2010-01-31 12:01:19.000'' and
    rd.eelink = 347
    order by rd.eeLink,ChqDate'

EXEC(@sql)

Open in new window

0
 

Author Comment

by:W.E.B
ID: 33607061
Hello,

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'Amount'.

Thanks,
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 33607176
What is the output from this?


DECLARE @sql varchar(8000)

SET @sql = 'Select Distinct rd.RegLink, rd.eeLink, rd.ChqType, rd.RegCode, rd.RegValue, 
    rd.RegAltValue, rd.AutoNumber, rd.CreatedDate, rd.EndDate, rd.idUser, 
    (Select r.ChqDate from Register r where r.RegLink = rd.RegLink) AS [ChqDate]'

SELECT @sql = @sql + 
    ', CASE WHEN RegCode = ' + RegCode + ' THEN RegCode ELSE NULL END AS Code' + RegCode + ', ' +
    'CASE WHEN RegCode = ' + RegCode + ' THEN rd.RegValue ELSE 0 END AS Code' + RegCode + 'Amount'
FROM RegData
GROUP BY RegCode
ORDER BY RegCode

SET @sql = @sql + ' From RegData r left Join 
    eeEmployee e on rd.eeLink = e.eeLink
    where rd.createddate between ''2010-01-01 12:01:19.000'' and ''2010-01-31 12:01:19.000'' and
    rd.eelink = 347
    order by rd.eeLink,ChqDate'

SELECT LEN(@sql)

Open in new window

0
 

Author Comment

by:W.E.B
ID: 33607228
Hello,

No Column Name
8000

Tx
0
 
LVL 93

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 200 total points
ID: 33607272
OK, the query string was getting too long, and was truncated.

Try this:


DECLARE @sql varchar(max)

SET @sql = 'Select Distinct rd.RegLink, rd.eeLink, rd.ChqType, rd.RegCode, rd.RegValue, 
    rd.RegAltValue, rd.AutoNumber, rd.CreatedDate, rd.EndDate, rd.idUser, 
    (Select r.ChqDate from Register r where r.RegLink = rd.RegLink) AS [ChqDate]'

SELECT @sql = @sql + 
    ', CASE WHEN RegCode = ' + RegCode + ' THEN RegCode ELSE NULL END AS Code' + RegCode + ', ' +
    'CASE WHEN RegCode = ' + RegCode + ' THEN rd.RegValue ELSE 0 END AS Code' + RegCode + 'Amount'
FROM RegData
GROUP BY RegCode
ORDER BY RegCode

SET @sql = @sql + ' From RegData r left Join 
    eeEmployee e on rd.eeLink = e.eeLink
    where rd.createddate between ''2010-01-01 12:01:19.000'' and ''2010-01-31 12:01:19.000'' and
    rd.eelink = 347
    order by rd.eeLink,ChqDate'

EXEC(@sql)

Open in new window

0
 

Author Comment

by:W.E.B
ID: 33607566
Hello,
ok, it says Command(s) completed successfully.,

How do I view it?
Thsnks,
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 33607661
Didn't you get a result set?

Please post the script *exactly* as you ran it.
0
 

Author Comment

by:W.E.B
ID: 33607682
Hello,
I ran it exactly as is.

DECLARE @sql varchar(max)

SET @sql = 'Select Distinct rd.RegLink, rd.eeLink, rd.ChqType, rd.RegCode, rd.RegValue,
    rd.RegAltValue, rd.AutoNumber, rd.CreatedDate, rd.EndDate, rd.idUser,
    (Select r.ChqDate from Register r where r.RegLink = rd.RegLink) AS [ChqDate]'

SELECT @sql = @sql +
    ', CASE WHEN RegCode = ' + RegCode + ' THEN RegCode ELSE NULL END AS Code' + RegCode + ', ' +
    'CASE WHEN RegCode = ' + RegCode + ' THEN rd.RegValue ELSE 0 END AS Code' + RegCode + 'Amount'
FROM RegData
GROUP BY RegCode
ORDER BY RegCode

SET @sql = @sql + ' From RegData r left Join
    eeEmployee e on rd.eeLink = e.eeLink
    where rd.createddate between ''2010-01-01 12:01:19.000'' and ''2010-01-31 12:01:19.000'' and
    rd.eelink = 347
    order by rd.eeLink,ChqDate'

EXEC(@sql)


Thansk
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 33607688
Do you have your options set to output results to the grid, or to a file?
0
 

Author Comment

by:W.E.B
ID: 33607758
Hello,
To Grid.

I just tried it again, same script,
it says
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'Amount'.

Thanks,
0
 
LVL 71

Expert Comment

by:Qlemo
ID: 33607860
there is no space before 'Amount'. Maybe that is the issue here.
0
 

Author Comment

by:W.E.B
ID: 33607874
Hello,
I Dont see any spaces before 'Amount'

-------- AS Code' + RegCode + 'Amount'
Tx
0
 
LVL 71

Expert Comment

by:Qlemo
ID: 33607946
Sorry, my bad, I did not fully understand the purpose of that construct. You want to get a column for each RegCode, which is alphanumeric. So you get e.g.
   CodeAA, CodeAAAmount, CodeBB, CodeBBAmount
aso.

There seems to be an issue with a particular RegCode, which is not valid as column name, maybe containing a space. Then the following should work:

...
SELECT @sql = @sql
+ ', CASE WHEN RegCode = ' + RegCode + ' THEN RegCode ELSE NULL END AS [Code' + RegCode + '], '
+   'CASE WHEN RegCode = ' + RegCode + ' THEN rd.RegValue ELSE 0 END AS [Code' + RegCode + 'Amount]'
...
0
 

Author Comment

by:W.E.B
ID: 33607985
Hello,
I Appreciate your help,

i USED THIS

DECLARE @sql varchar(max)

SET @sql = 'Select Distinct rd.RegLink, rd.eeLink, rd.ChqType, rd.RegCode, rd.RegValue,
    rd.RegAltValue, rd.AutoNumber, rd.CreatedDate, rd.EndDate, rd.idUser,
    (Select r.ChqDate from Register r where r.RegLink = rd.RegLink) AS [ChqDate]'

SELECT @sql = @sql
+ ', CASE WHEN RegCode = ' + RegCode + ' THEN RegCode ELSE NULL END AS [Code' + RegCode + '], '
+   'CASE WHEN RegCode = ' + RegCode + ' THEN rd.RegValue ELSE 0 END AS [Code' + RegCode + 'Amount]'

FROM RegData
GROUP BY RegCode
ORDER BY RegCode

SET @sql = @sql + ' From RegData rd left Join
    eeEmployee e on rd.eeLink = e.eeLink
    where rd.createddate between ''2010-01-01 12:01:19.000'' and ''2010-01-31 12:01:19.000'' and
    rd.eelink = 347
    order by rd.eeLink,ChqDate'

EXEC(@sql)

i get error:
Msg 207, Level 16, State 1, Line 3
Invalid column name 'G10'.
Msg 207, Level 16, State 1, Line 3
Invalid column name 'G10'.

These are the RegCode ( some are numbers, some start with Letter),

Also,
any way to sum the values of each code?
even if we have to create a new column, or a Sum header per column

thanks,
0
 
LVL 71

Expert Comment

by:Qlemo
ID: 33608000
Please print out the @sql var before EXEC, so we can see what is generated.
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 33608033
Wass_QA,

1) I think you'd better upload some dummy data for your three tables RegData, eeEmployee, and Register.  Make sure you do not post any sensitive/confidential data.  Use "fake" data if necessary

2) As for getting a sum, I think you should stick to your original question, and pursue summing the columns in a new question once this one is resolved

Patrick
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33609009
the error message makes me think that 'G10' is the value of Regcode, right?

DECLARE @sql varchar(max)

SET @sql = 'Select Distinct rd.RegLink, rd.eeLink, rd.ChqType, rd.RegCode, rd.RegValue,
    rd.RegAltValue, rd.AutoNumber, rd.CreatedDate, rd.EndDate, rd.idUser,
    (Select r.ChqDate from Register r where r.RegLink = rd.RegLink) AS [ChqDate]'

SELECT @sql = @sql +
    ', CASE WHEN RegCode = ''' + RegCode + ''' THEN RegCode ELSE NULL END AS Code' + RegCode + ' ' +
    ', CASE WHEN RegCode = ''' + RegCode + ''' THEN rd.RegValue ELSE 0 END AS Code' + RegCode + 'Amount '
FROM RegData
GROUP BY RegCode
ORDER BY RegCode

SET @sql = @sql + ' From RegData r left Join
    eeEmployee e on rd.eeLink = e.eeLink
    where rd.createddate between ''2010-01-01 12:01:19.000'' and ''2010-01-31 12:01:19.000'' and
    rd.eelink = 347
    order by rd.eeLink,ChqDate'

EXEC(@sql)

Open in new window

0
 

Author Comment

by:W.E.B
ID: 33610728
Hello,
Correct, the 'G10'  Is the value of the Regcode

I ran what you've sent,
DECLARE @sql varchar(max)

SET @sql = 'Select Distinct rd.RegLink, rd.eeLink, rd.ChqType, rd.RegCode, rd.RegValue,
    rd.RegAltValue, rd.AutoNumber, rd.CreatedDate, rd.EndDate, rd.idUser,
    (Select r.ChqDate from Register r where r.RegLink = rd.RegLink) AS [ChqDate]'

SELECT @sql = @sql +
    ', CASE WHEN RegCode = ''' + RegCode + ''' THEN RegCode ELSE NULL END AS Code' + RegCode + ' ' +
    ', CASE WHEN RegCode = ''' + RegCode + ''' THEN rd.RegValue ELSE 0 END AS Code' + RegCode + 'Amount '
FROM RegData
GROUP BY RegCode
ORDER BY RegCode

SET @sql = @sql + ' From RegData r left Join
    eeEmployee e on rd.eeLink = e.eeLink
    where rd.createddate between ''2010-01-01 12:01:19.000'' and ''2010-01-31 12:01:19.000'' and
    rd.eelink = 347
    order by rd.eeLink,ChqDate'

EXEC(@sql)


I get error
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'Amount'.

I changed to (as per previous)
SELECT @sql = @sql
+ ', CASE WHEN RegCode = ' + RegCode + ' THEN RegCode ELSE NULL END AS [Code' + RegCode + '], '
+   'CASE WHEN RegCode = ' + RegCode + ' THEN rd.RegValue ELSE 0 END AS [Code' + RegCode + 'Amount]'

I get the error
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "rd.eeLink" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "rd.createddate" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "rd.createddate" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "rd.eelink" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "rd.RegLink" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "rd.eeLink" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "rd.ChqType" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "rd.RegCode" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "rd.RegValue" could not be bound.
Msg 4104, Level 16, State 1, Line 1 ---------------

If I change your Join
SET @sql = @sql + ' From RegData r left Join
TO
SET @sql = @sql + ' From RegData rd left Join

I get error
Msg 207, Level 16, State 1, Line 3
Invalid column name 'G10'.
Msg 207, Level 16, State 1, Line 3

Thanks.
0
 
LVL 71

Expert Comment

by:Qlemo
ID: 33611048
After the last change, please print out the @sql var before EXEC, so we can see what is generated.
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 33611167
Yes, and please post some dummy data for testing purposes.

In the future, please be sure to give an accurate description of what you are trying to do.  The original SQL I posted did *exactly* what you indicated you wanted in your question; it was something of a shock for you to say that your actual situation was not at all as you described it initially.
0
 
LVL 71

Expert Comment

by:Qlemo
ID: 33612300
I agree. There is an unexpected expansion of the question in http:#a33606643, instead of concentrating on the issue as-is - no data, or syntax error.
0

Featured Post

Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question