January 31, 2016 at 9:12 pm
i have scenario ,SalaryCode,SalaryName
salarycode salaryname
1 basic
1 DA
1 PF
2 contributions
2 deductions
I want to get in query
1 basic 2 contributions
1 DA 2 Deductions
1 PF
Thanks
Chandran
February 1, 2016 at 8:20 am
ramachandran narayanan (1/31/2016)
i have scenario ,SalaryCode,SalaryNamesalarycode salaryname
1 basic
1 DA
1 PF
2 contributions
2 deductions
I want to get in query
1 basic 2 contributions
1 DA 2 Deductions
1 PF
Thanks
Chandran
The reason nobody has assisted you yet is because you need to provide some details here. I am 100% confident there are dozens of people around here that can help you resolve this quickly, easily and efficiently. However, without more details we are all left guessing at what you may or may not want.
Here is a great article that explains the types of things that would make this a more answerable question. http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 1, 2016 at 1:13 pm
Yes, this can be done. Based on my understanding of your requirements, you would need the following ingredients:
* Two CTEs. One for the code 1 rows, one for the code 2 rows. Both should use a ROW_NUMBER() expression to add a row number based on the sort order you want to impose (probably from looking at your sample data alphabetic by name).
* A query that joins those two CTEs on the generated row numbers. If we assume that you don't know which of the two sets will have more rows, this will have to be a full outer join. And that means a lot of COALESCE() fun in the SELECT list to replace the possible null values in each column with blanks.
The query you end up with will be long; ugly; hard to understand; hard to maintain; and if your data is large enough, slow. And you'll have to test it with all possible options of same amount type 1 and type 2, more type 1, more type 2, no type 1 at all, and no typpe 2 at all.
Or you could consider using a report generator or other front end tool to do the pretty formatting. Honestly, they are much better suited for the job.
February 1, 2016 at 1:25 pm
did these responses answer your question?
http://stackoverflow.com/questions/35123166/how-to-list-code-in-2-separate-columns-using-a-sql-query
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
February 1, 2016 at 2:56 pm
J Livingston SQL (2/1/2016)
did these responses answer your question?http://stackoverflow.com/questions/35123166/how-to-list-code-in-2-separate-columns-using-a-sql-query
All these questions on different forums and no clear definition of how to build the output. Posting how to derive the desired set would go a long way in getting a solution that works.
Hugo, I must admit that seeing the row positions is something I would not have seen.
February 1, 2016 at 3:18 pm
(deleted - had not read the post well enough so my response made no sense)
February 1, 2016 at 3:23 pm
Ed Wagner (2/1/2016)
J Livingston SQL (2/1/2016)
did these responses answer your question?http://stackoverflow.com/questions/35123166/how-to-list-code-in-2-separate-columns-using-a-sql-query
All these questions on different forums and no clear definition of how to build the output. Posting how to derive the desired set would go a long way in getting a solution that works.
Hugo, I must admit that seeing the row positions is something I would not have seen.
Unfortunately, I have seen similar questions more often so I recognised the pattern.
I think I handed the OP everything needed to construct a solution. I tihnk (s)he'll learn more from piecing it together him/herself than when I would spoonfeed a full solution.
Or they can go with one of the solutions posted on those other locations (probably all by the same person, or we have just found a full class of cheating students), and find that it does not work correct in all cases once it has already been deployed to production.
February 2, 2016 at 1:14 am
select T1.salaryname,T2.salaryname from
(
select ROW_NUMBER() over(order by salarycode ) as No,cast(salarycode as varchar)+salaryname as salaryname from basic where salarycode=1) T1
left join
(select ROW_NUMBER() over(order by salarycode ) as No,cast(salarycode as varchar)+salaryname as salaryname from basic where salarycode=2) T2
on T1.No=T2.No
--chalam
February 2, 2016 at 1:05 pm
Note of warning to ramachandran narayanan:
The solution posted by chalam87 (which is about equal to a solution posted to one of the other locations where you asked this question) will return correct results for the test data, but might not return correct results for all possible data. Allthough I of course have no idea what kind of data is or is not possible in your system.
February 2, 2016 at 1:52 pm
Hugo Kornelis (2/2/2016)
Note of warning to ramachandran narayanan:The solution posted by chalam87 (which is about equal to a solution posted to one of the other locations where you asked this question) will return correct results for the test data, but might not return correct results for all possible data. Allthough I of course have no idea what kind of data is or is not possible in your system.
Given the vague question and lack of response, my guess is that it was probably for a class.
February 2, 2016 at 1:57 pm
Ed Wagner (2/2/2016)
Hugo Kornelis (2/2/2016)
Note of warning to ramachandran narayanan:The solution posted by chalam87 (which is about equal to a solution posted to one of the other locations where you asked this question) will return correct results for the test data, but might not return correct results for all possible data. Allthough I of course have no idea what kind of data is or is not possible in your system.
Given the vague question and lack of response, my guess is that it was probably for a class.
Which is exactly the reason (well, one of them) why I didn't post a complete solution. No problem helping students with their homework, I know there's lots of terrible teachers out there, but I do have a problem with doing it for them.
And which is also why I was unhappy to see that someone else did post a full solution.
Still not sure if I am happy or not that it was actually an incorrect solution.
Also, not happy to see that students still get homework assignment that suggest that it might be a good idea to do this kind of formatting on the database.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply