SQL

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
W.E.BAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
chrisghallConnect With a Mentor Commented:
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
 
Patrick MatthewsCommented:
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
 
W.E.BAuthor Commented:
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
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

 
Patrick MatthewsCommented:
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
 
W.E.BAuthor Commented:
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
 
W.E.BAuthor Commented:
Hello,
Your help is apprciated.
I don't know how to view the results.

Thanks,
0
 
Patrick MatthewsCommented:
>>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
 
W.E.BAuthor Commented:
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
 
W.E.BAuthor Commented:
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
 
Patrick MatthewsCommented:
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
 
W.E.BAuthor Commented:
Hello,

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

Thanks,
0
 
Patrick MatthewsCommented:
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
 
W.E.BAuthor Commented:
Hello,

No Column Name
8000

Tx
0
 
Patrick MatthewsConnect With a Mentor Commented:
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
 
W.E.BAuthor Commented:
Hello,
ok, it says Command(s) completed successfully.,

How do I view it?
Thsnks,
0
 
Patrick MatthewsCommented:
Didn't you get a result set?

Please post the script *exactly* as you ran it.
0
 
W.E.BAuthor Commented:
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
 
Patrick MatthewsCommented:
Do you have your options set to output results to the grid, or to a file?
0
 
W.E.BAuthor Commented:
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
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
there is no space before 'Amount'. Maybe that is the issue here.
0
 
W.E.BAuthor Commented:
Hello,
I Dont see any spaces before 'Amount'

-------- AS Code' + RegCode + 'Amount'
Tx
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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
 
W.E.BAuthor Commented:
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
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Please print out the @sql var before EXEC, so we can see what is generated.
0
 
Patrick MatthewsCommented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
W.E.BAuthor Commented:
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
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
After the last change, please print out the @sql var before EXEC, so we can see what is generated.
0
 
Patrick MatthewsCommented:
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
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.