×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Concatenation or pivot in SQL

Concatenation or pivot in SQL

Concatenation or pivot in SQL

(OP)
I deleted my old post and reposting since I did not make myself clear what I was looking to do.

I have a 4800 approx. row database in SQL Server. I need to either take that data and in SQL Server perform SQL code to be able to pivot the data. An example of some pivot code is:
SELECT 'AverageCost' AS Cost_Sorted_By_Production_Days,
[0], [1], [2], [3], [4]
FROM
(SELECT DaysToManufacture, StandardCost
FROM Production.Product) AS SourceTable
PIVOT
(
AVG(StandardCost)
FOR DaysToManufacture IN ([0], [1], [2], [3], [4])
) AS PivotTable;

I have attempted to apply this methodology but I have too many rows and not sure quite how to get what I need done. So my option number 2 was to try concatenation in Excel.

I thought I could do an extract of the raw data into Excel and then some sort of concatenation formula to say if Column A is the same concatenate all the other columns associated. So if Column A2 through A3 is identical concatenate anything in B2 through X3. Everything varies. I attached an example of an extract from the database that is the data have and then below is an example of the need. I don't want to have to do an extract and manually do all this. I am sure there is a way just not sure of that way and what the right process or path is to take.

RE: Concatenation or pivot in SQL

Hi, tmcrouse (Programmer)

Good news. You can program a solution in Excel VBA.

I'd suggest asking for help in forum707: VBA Visual Basic for Applications (Microsoft).

However, I have a question regarding

Access2Care | Simply Health | Call Center, Claims, Credentialing, Member Outreach, Provider Outreach, Network,

...however, column B for Access2Care has either Simply Health or NOTHING. The column C values for Simply Health only has 4 values, not 5 (not Network).

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Concatenation or pivot in SQL

(OP)
Thanks, I actually figured out a way to do it with separate pivots in sql server. I opted for this solution because this is going to change time and time again the more the folks that update sharepoint add data and I have to align as well as make what they change in sharepoint look right in the backend database. Because in the end sharepoint they want 1 row per delegatename but in backend it is distinct across many things. so this is part of what I did


/*now work with the multiple delegatesfunction to find out how many delegatesfunction are per delegatesname do a count*/
select distinct delegatesname, delegatesfunction into deletest from deleb where msoname is not null group by DelegatesName, delegatesfunction;
select distinct delegatesname, COUNT(delegatesfunction) as a from deletest group by DelegatesName order by a desc;/*the most right now is 10*/

select distinct delegatesname, delegatesfunction1, delegatesfunction2, delegatesfunction3, delegatesfunction4, delegatesfunction5, delegatesfunction6, delegatesfunction7, delegatesfunction8,
delegatesfunction9, delegatesfunction10 into deled/ *holds the delegatesfunction retain until last final query*/
from (select delegatesname, delegatesfunction, 'delegatesfunction' + CAST(row_number() over(partition by delegatesname order by delegatesname) as varchar(50))columnsequence
from deletest
) temp pivot(max(delegatesfunction)
for columnsequence in (delegatesfunction1, delegatesfunction2, delegatesfunction3, delegatesfunction4, delegatesfunction5, delegatesfunction6, delegatesfunction7, delegatesfunction8,
delegatesfunction9, delegatesfunction10)) piv;

drop table deletest;

RE: Concatenation or pivot in SQL

This assumes a max of 10 rows of column C values.

Might just BREAK if that row count is ever exceeded.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close