Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SQL table to single row Spreadsheet or flatfile 1

Status
Not open for further replies.
jfcox

jfcox

Programmer
Joined
Jul 11, 2001
Messages
44
Location
US
I have a typical table that looks something like

CustNo State PartNo
1 NJ 60
1 NY 50
2 TN 30
2 KY 45

The CustNo column is not unique but is the key.

I need to create a single row on a spreadsheet or flat file that looks like this

CustNo State1 PartNo1 State2 PartNo2 , etc. for as many rows that are returned.

I can do this programatically with VB or something but I'm wondering if there is a way to do this directly from SQL and avoid all the processing overhead

Thanks in advance
 
Sort by date Sort by votes
Can there be a CustNo, State that is duplicated too. What do you want done then?
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]
 
Upvote 0 Downvote
Using the previous example, the result I need should look something like

CustNo state1 partno1 state2 partno 2
1 NJ 60 NY 50
2 TN 30 KY 45

The state might be repeated but with a different PartNo. Technically, there can be unlimited numbers of states but in practical terms the most is typically 5 or 6.

Is that clearer?

Thanks for your help
 
Upvote 0 Downvote
In the event that state is repeated with different part number, do you want that added to the row?
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]
 
Upvote 0 Downvote
No, that doesn't matter.

Thanks
 
Upvote 0 Downvote
Does it matter which of multiple part numbers gets displayed for a given state?
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]
 
Upvote 0 Downvote
Yes, they are essentially name-value pairs.
 
Upvote 0 Downvote
So which one do you want displayed...you said you only want one per state right?
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]
 
Upvote 0 Downvote
What we need to know is, if you have this:

Code: 
CustNo State PartNo
1       NJ    60
1       NY    50
1       NY    30
2       TN    30
2       KY    45

How do we determine what you want to see as a result?

-SQLBill

Posting advice: FAQ481-4875
 
Upvote 0 Downvote
Obviously, I picked a bad example. Let me try with somthing that might inherently imply the outcome.

Suppose I had a table of last_Name, Drivers Lic St., and Drivers License number. If I had data like this:

Smith NJ 12345
Smith NY 98755
Jones TN 55554
Jones KY 6879

I'm looking for a flat file result that would have lines like this:

Smith,NJ,12345,NY,98755
Jones,TN,5554,KY,6879

The number of licenses is not known in advance and is not limited. However, the State/License number combination would be unique.

Clearer?

Thanks to all for the help
 
Upvote 0 Downvote
Just to make sure we are clear....one person can have a license in more than one state:

Smith NJ 12345
Smith NY 98755
Click to expand...

but can NOT have two or more licenses from the same state:

Smith NJ 12345
Smith NY 98755
Smith NY 98811
Click to expand...

Is that correct? You can have the first example but NOT the second?

-SQLbill

Posting advice: FAQ481-4875
 
Upvote 0 Downvote
Create this SP and it will generate a crosstab query for you. It's not exactly what you want, but it might do.
Code: 
[Blue]CREATE[/Blue] [Blue]PROCEDURE[/Blue] CrossTabPrint 
   @QueryTemplate [Blue]varchar[/Blue][Gray]([/Gray]8000[Gray])[/Gray][Gray],[/Gray]
   @AggregateFunction [Blue]varchar[/Blue][Gray]([/Gray]100[Gray])[/Gray][Gray],[/Gray] 
   @PivotColumn [Blue]varchar[/Blue][Gray]([/Gray]100[Gray])[/Gray]
[Blue]AS[/Blue]
   [Blue]SET[/Blue] [Blue]NoCount[/Blue] [Blue]ON[/Blue]
   [Blue]SET[/Blue] [Blue]Ansi_Warnings[/Blue] [Blue]Off[/Blue]
   [Blue]DECLARE[/Blue] @SqlCase [Blue]varchar[/Blue][Gray]([/Gray]8000[Gray])[/Gray][Gray],[/Gray] 
           @SqlPivot [Blue]varchar[/Blue][Gray]([/Gray]8000[Gray])[/Gray][Gray],[/Gray] @Delimiter [Blue]varchar[/Blue][Gray]([/Gray]1[Gray])[/Gray]
   [Blue]DECLARE[/Blue] @ChrPos [Blue]int[/Blue] [green]--Character position
[/green]   [Blue]DECLARE[/Blue] @Aggs [Blue]varchar[/Blue][Gray]([/Gray]64[Gray])[/Gray][Gray],[/Gray] @BeforeAgg [Blue]varchar[/Blue][Gray]([/Gray]8000[Gray])[/Gray][Gray],[/Gray]
           @AfterAgg [Blue]varchar[/Blue] [Gray]([/Gray]8000[Gray])[/Gray]
   [Blue]DECLARE[/Blue] @LftPos [Blue]int[/Blue][Gray],[/Gray] @RgtPos [Blue]int[/Blue]
[green]
   --Delete white space
[/green]   [Blue]SET[/Blue] @QueryTemplate[Gray]=[/Gray][Fuchsia]LTrim[/Fuchsia][Gray]([/Gray][Fuchsia]Rtrim[/Fuchsia][Gray]([/Gray]@QueryTemplate[Gray])[/Gray][Gray])[/Gray]
   [Blue]SET[/Blue] @AggregateFunction[Gray]=[/Gray][Fuchsia]LTrim[/Fuchsia][Gray]([/Gray][Fuchsia]Rtrim[/Fuchsia][Gray]([/Gray]@AggregateFunction[Gray])[/Gray][Gray])[/Gray]
   [Blue]SET[/Blue] @PivotColumn[Gray]=[/Gray][Fuchsia]LTrim[/Fuchsia][Gray]([/Gray][Fuchsia]Rtrim[/Fuchsia][Gray]([/Gray]@PivotColumn[Gray])[/Gray][Gray])[/Gray]
   [Blue]SET[/Blue] @ChrPos[Gray]=[/Gray]0
   [Blue]WHILE[/Blue] [Fuchsia]Len[/Fuchsia][Gray]([/Gray]@QueryTemplate[Gray])[/Gray][Gray]<[/Gray][Gray]>[/Gray]@ChrPos [Blue]BEGIN[/Blue]
      [Blue]SET[/Blue] @ChrPos[Gray]=[/Gray][Fuchsia]Len[/Fuchsia][Gray]([/Gray]@QueryTemplate[Gray])[/Gray]
      [Blue]SET[/Blue] @QueryTemplate[Gray]=[/Gray][Fuchsia]Replace[/Fuchsia][Gray]([/Gray]@QueryTemplate[Gray],[/Gray][red]'  '[/red][Gray],[/Gray][red]' '[/red][Gray])[/Gray]
   [Blue]END[/Blue]
   [Blue]SET[/Blue] @ChrPos[Gray]=[/Gray]0
   [Blue]WHILE[/Blue] [Fuchsia]Len[/Fuchsia][Gray]([/Gray]@AggregateFunction[Gray])[/Gray][Gray]<[/Gray][Gray]>[/Gray]@ChrPos [Blue]BEGIN[/Blue]
      [Blue]SET[/Blue] @ChrPos[Gray]=[/Gray][Fuchsia]Len[/Fuchsia][Gray]([/Gray]@AggregateFunction[Gray])[/Gray]
      [Blue]SET[/Blue] @AggregateFunction[Gray]=[/Gray][Fuchsia]Replace[/Fuchsia][Gray]([/Gray]@AggregateFunction[Gray],[/Gray][red]'  '[/red][Gray],[/Gray][red]' '[/red][Gray])[/Gray]
      [Blue]SET[/Blue] @AggregateFunction[Gray]=[/Gray][Fuchsia]Replace[/Fuchsia][Gray]([/Gray]@AggregateFunction[Gray],[/Gray][red]' ('[/red][Gray],[/Gray][red]'('[/red][Gray])[/Gray]
      [Blue]SET[/Blue] @AggregateFunction[Gray]=[/Gray][Fuchsia]Replace[/Fuchsia][Gray]([/Gray]@AggregateFunction[Gray],[/Gray][red]'( '[/red][Gray],[/Gray][red]'('[/red][Gray])[/Gray]
      [Blue]SET[/Blue] @AggregateFunction[Gray]=[/Gray][Fuchsia]Replace[/Fuchsia][Gray]([/Gray]@AggregateFunction[Gray],[/Gray][red]') '[/red][Gray],[/Gray][red]')'[/red][Gray])[/Gray]
      [Blue]SET[/Blue] @AggregateFunction[Gray]=[/Gray][Fuchsia]Replace[/Fuchsia][Gray]([/Gray]@AggregateFunction[Gray],[/Gray][red]' )'[/red][Gray],[/Gray][red]')'[/red][Gray])[/Gray]
   [Blue]END[/Blue]
   [Blue]SET[/Blue] @ChrPos[Gray]=[/Gray]0
   [Blue]WHILE[/Blue] [Fuchsia]Len[/Fuchsia][Gray]([/Gray]@PivotColumn[Gray])[/Gray][Gray]<[/Gray][Gray]>[/Gray]@ChrPos [Blue]BEGIN[/Blue]
      [Blue]SET[/Blue] @ChrPos[Gray]=[/Gray][Fuchsia]Len[/Fuchsia][Gray]([/Gray]@PivotColumn[Gray])[/Gray]
      [Blue]SET[/Blue] @PivotColumn[Gray]=[/Gray][Fuchsia]Replace[/Fuchsia][Gray]([/Gray]@PivotColumn[Gray],[/Gray][red]'  '[/red][Gray],[/Gray][red]' '[/red][Gray])[/Gray]
      [Blue]SET[/Blue] @PivotColumn[Gray]=[/Gray][Fuchsia]Replace[/Fuchsia][Gray]([/Gray]@PivotColumn[Gray],[/Gray][red]' ('[/red][Gray],[/Gray][red]'('[/red][Gray])[/Gray]
      [Blue]SET[/Blue] @PivotColumn[Gray]=[/Gray][Fuchsia]Replace[/Fuchsia][Gray]([/Gray]@PivotColumn[Gray],[/Gray][red]'( '[/red][Gray],[/Gray][red]'('[/red][Gray])[/Gray]
      [Blue]SET[/Blue] @PivotColumn[Gray]=[/Gray][Fuchsia]Replace[/Fuchsia][Gray]([/Gray]@PivotColumn[Gray],[/Gray][red]') '[/red][Gray],[/Gray][red]')'[/red][Gray])[/Gray]
      [Blue]SET[/Blue] @PivotColumn[Gray]=[/Gray][Fuchsia]Replace[/Fuchsia][Gray]([/Gray]@PivotColumn[Gray],[/Gray][red]' )'[/red][Gray],[/Gray][red]')'[/red][Gray])[/Gray]
   [Blue]END[/Blue]
[green]
   ---Check for valid parameters in this section---
[/green]   [green]---Look in the right place for the output---
[/green]   [Blue]SELECT[/Blue] [red]'Check the Message Tab for your output.'[/red] 
   [Blue]EXEC[/Blue][Gray]([/Gray][red]'SET ParseOnly ON '[/red] [Gray]+[/Gray] @QueryTemplate[Gray])[/Gray]
   [Blue]IF[/Blue] [Fuchsia]@@Error[/Fuchsia][Gray]>[/Gray]0 [Blue]BEGIN[/Blue]
      [Blue]PRINT[/Blue] [red]'There is an error in Parameter #1'[/red]
      [Blue]RETURN[/Blue]
   [Blue]END[/Blue]

   [Blue]SET[/Blue] @ChrPos [Gray]=[/Gray] [Fuchsia]CharIndex[/Fuchsia][Gray]([/Gray][red]'select '[/red][Gray],[/Gray]@QueryTemplate[Gray])[/Gray]
   [Blue]IF[/Blue] @ChrPos[Gray]=[/Gray]0 [Blue]BEGIN[/Blue]
      [Blue]PRINT[/Blue] [red]'You didn''t enter a SELECT clause in the 1st parameter.'[/red]
      [Blue]RETURN[/Blue]
   [Blue]END[/Blue]
   [Blue]ELSE[/Blue] [Blue]BEGIN[/Blue] [green]--May as well capatilize the keywords for looks.
[/green]      [Blue]SET[/Blue] @QueryTemplate [Gray]=[/Gray] [Fuchsia]Stuff[/Fuchsia][Gray]([/Gray]@QueryTemplate[Gray],[/Gray]@ChrPos[Gray],[/Gray]6[Gray],[/Gray][red]'SELECT'[/red][Gray])[/Gray]
   [Blue]END[/Blue]

   [Blue]SET[/Blue] @BeforeAgg[Gray]=[/Gray][red]'SET ParseOnly ON '[/red] [Gray]+[/Gray] 
      [Fuchsia]Stuff[/Fuchsia][Gray]([/Gray]@QueryTemplate[Gray],[/Gray]@ChrPos[Gray]+[/Gray]7[Gray],[/Gray]0[Gray],[/Gray]@AggregateFunction[Gray]+[/Gray][red]', '[/red][Gray])[/Gray]
   [Blue]EXEC[/Blue] [Gray]([/Gray]@BeforeAgg[Gray])[/Gray]
   [Blue]IF[/Blue] [Fuchsia]@@Error[/Fuchsia][Gray]>[/Gray]0 [Blue]BEGIN[/Blue]
      [Blue]PRINT[/Blue] [red]'There is an error in Parameter #2.'[/red]
      [Blue]RETURN[/Blue]
   [Blue]END[/Blue]

   [Blue]SET[/Blue] @ChrPos [Gray]=[/Gray] [Fuchsia]CharIndex[/Fuchsia][Gray]([/Gray][red]' from '[/red][Gray],[/Gray]@QueryTemplate[Gray])[/Gray]
   [Blue]IF[/Blue] @ChrPos[Gray]=[/Gray]0 [Blue]BEGIN[/Blue]
      [Blue]PRINT[/Blue] [red]'You didn''t enter a FROM clause in the 1st parameter.'[/red]
      [Blue]RETURN[/Blue]
   [Blue]END[/Blue]
   [Blue]ELSE[/Blue] [Blue]BEGIN[/Blue]
      [Blue]SET[/Blue] @QueryTemplate [Gray]=[/Gray] [Fuchsia]Stuff[/Fuchsia][Gray]([/Gray]@QueryTemplate[Gray],[/Gray]@ChrPos[Gray],[/Gray]5[Gray],[/Gray][red]' FROM'[/red][Gray])[/Gray]
   [Blue]END[/Blue]

   [Blue]SET[/Blue] @ChrPos [Gray]=[/Gray] [Fuchsia]CharIndex[/Fuchsia][Gray]([/Gray][red]' group by '[/red][Gray],[/Gray]@QueryTemplate[Gray])[/Gray]
   [Blue]IF[/Blue] @ChrPos[Gray]=[/Gray]0 [Blue]BEGIN[/Blue]
      [Blue]PRINT[/Blue] [red]'You didn''t enter a GROUP BY clause in the 1st parameter.'[/red]
      [Blue]RETURN[/Blue]
   [Blue]END[/Blue]
   [Blue]ELSE[/Blue] [Blue]BEGIN[/Blue]
      [Blue]SET[/Blue] @QueryTemplate [Gray]=[/Gray] [Fuchsia]Stuff[/Fuchsia][Gray]([/Gray]@QueryTemplate[Gray],[/Gray]@ChrPos[Gray],[/Gray]10[Gray],[/Gray][Fuchsia]Char[/Fuchsia][Gray]([/Gray]13[Gray])[/Gray][Gray]+[/Gray][red]'   GROUP BY '[/red][Gray])[/Gray]
   [Blue]END[/Blue]
[green]
   -- Just to make the code look good.
[/green]   [Blue]SET[/Blue] @ChrPos [Gray]=[/Gray] [Fuchsia]CharIndex[/Fuchsia][Gray]([/Gray][red]' where '[/red][Gray],[/Gray]@QueryTemplate[Gray])[/Gray]
   [Blue]IF[/Blue] @ChrPos[Gray]>[/Gray]0 [Blue]SET[/Blue] @QueryTemplate [Gray]=[/Gray] [Fuchsia]Stuff[/Fuchsia][Gray]([/Gray]@QueryTemplate[Gray],[/Gray]@ChrPos[Gray],[/Gray]7[Gray],[/Gray][Fuchsia]Char[/Fuchsia][Gray]([/Gray]13[Gray])[/Gray][Gray]+[/Gray][red]'   WHERE '[/red][Gray])[/Gray]

   [Blue]SET[/Blue] @ChrPos [Gray]=[/Gray] [Fuchsia]CharIndex[/Fuchsia][Gray]([/Gray][red]' having '[/red][Gray],[/Gray]@QueryTemplate[Gray])[/Gray]
   [Blue]IF[/Blue] @ChrPos[Gray]>[/Gray]0 [Blue]SET[/Blue] @QueryTemplate [Gray]=[/Gray] [Fuchsia]Stuff[/Fuchsia][Gray]([/Gray]@QueryTemplate[Gray],[/Gray]@ChrPos[Gray],[/Gray]8[Gray],[/Gray][Fuchsia]Char[/Fuchsia][Gray]([/Gray]13[Gray])[/Gray][Gray]+[/Gray][red]'   HAVING '[/red][Gray])[/Gray]

   [Blue]SET[/Blue] @ChrPos [Gray]=[/Gray] [Fuchsia]CharIndex[/Fuchsia][Gray]([/Gray][red]' order by '[/red][Gray],[/Gray]@QueryTemplate[Gray])[/Gray] 
   [Blue]IF[/Blue] @ChrPos[Gray]>[/Gray]0 [Blue]SET[/Blue] @QueryTemplate [Gray]=[/Gray] [Fuchsia]Stuff[/Fuchsia][Gray]([/Gray]@QueryTemplate[Gray],[/Gray]@ChrPos[Gray],[/Gray]10[Gray],[/Gray][Fuchsia]Char[/Fuchsia][Gray]([/Gray]13[Gray])[/Gray][Gray]+[/Gray][red]'   ORDER BY '[/red][Gray])[/Gray]

   [Blue]IF[/Blue] [Fuchsia]CharIndex[/Fuchsia][Gray]([/Gray][red]'='[/red][Gray],[/Gray]@PivotColumn[Gray])[/Gray][Gray]>[/Gray]0  
      [Blue]PRINT[/Blue] [red]'Warning: do not use an alias or a Named Column in parameter #3 (@PivotColumn). If you did, the SP will crash.'[/red][green]
------  Finished validity checking  -------
[/green][green]
----This section devoted to @AggregateFunction parameter only ----
[/green]   [Blue]CREATE[/Blue] [Blue]TABLE[/Blue] #AggFunctions [Gray]([/Gray]Agg [Blue]varchar[/Blue][Gray]([/Gray]11[Gray])[/Gray][Gray])[/Gray]
   [Blue]SET[/Blue] @Aggs[Gray]=[/Gray][red]'Avg(,Count(,Count_Big(,Max(,Min(,StDev(,StDevP(,Sum(,Var(,VarP(,'[/red][green]
   --Build a temporary table of all the agg functions
[/green]   [Blue]WHILE[/Blue] @Aggs[Gray]<[/Gray][Gray]>[/Gray][red]''[/red] [Blue]BEGIN[/Blue] 
      [Blue]SET[/Blue] @ChrPos[Gray]=[/Gray][Fuchsia]CharIndex[/Fuchsia][Gray]([/Gray][red]','[/red][Gray],[/Gray]@Aggs[Gray])[/Gray]
      [Blue]INSERT[/Blue] [Blue]INTO[/Blue] #AggFunctions
         [Blue]SELECT[/Blue] [Fuchsia]Left[/Fuchsia][Gray]([/Gray]@Aggs[Gray],[/Gray]@ChrPos[Gray]-[/Gray]1[Gray])[/Gray]
      [Blue]SET[/Blue] @Aggs[Gray]=[/Gray][Fuchsia]SubString[/Fuchsia][Gray]([/Gray]@Aggs[Gray],[/Gray]@ChrPos[Gray]+[/Gray]1[Gray],[/Gray]64[Gray])[/Gray]
   [Blue]END[/Blue]

   [Blue]SET[/Blue] @LftPos[Gray]=[/Gray]0  [green]--Find the location of the aggregate function
[/green]   [Blue]SELECT[/Blue] @RgtPos[Gray]=[/Gray][Fuchsia]CharIndex[/Fuchsia][Gray]([/Gray]Agg[Gray],[/Gray]@AggregateFunction[Gray])[/Gray][Gray],[/Gray]@LftPos[Gray]=[/Gray]@LftPos[Gray]+[/Gray]@RgtPos [Gray],[/Gray]
                  @AggregateFunction[Gray]=[/Gray][Fuchsia]Stuff[/Fuchsia][Gray]([/Gray]@AggregateFunction[Gray],[/Gray]@RgtPos[Gray],[/Gray][Fuchsia]Len[/Fuchsia][Gray]([/Gray]Agg[Gray])[/Gray][Gray],[/Gray]Agg[Gray])[/Gray] [green]--May as well format it correctly
[/green]      [Blue]FROM[/Blue] #AggFunctions [Blue]WHERE[/Blue] [Fuchsia]CharIndex[/Fuchsia][Gray]([/Gray]Agg[Gray],[/Gray]@AggregateFunction[Gray])[/Gray][Gray]>[/Gray]0
   [Blue]DROP[/Blue] [Blue]TABLE[/Blue] #AggFunctions
   [Blue]IF[/Blue] @RgtPos[Gray]<[/Gray][Gray]>[/Gray]@LftPos 
      [Blue]PRINT[/Blue] [red]'You can''t have more than one aggregate function in Parameter #2.'[/red]
   [Blue]IF[/Blue] @RgtPos[Gray]=[/Gray]0
      [Blue]PRINT[/Blue] [red]'You didn''t enter an aggregate function in Parameter #2.'[/red][green]
   --Get any non-agg functions that occur prior to the agg function
[/green]   [Blue]SET[/Blue] @BeforeAgg[Gray]=[/Gray][Fuchsia]Left[/Fuchsia][Gray]([/Gray]@AggregateFunction[Gray],[/Gray]@RgtPos[Gray]-[/Gray]1[Gray])[/Gray] 
   [Blue]SET[/Blue] @AggregateFunction[Gray]=[/Gray][Fuchsia]SubString[/Fuchsia][Gray]([/Gray]@AggregateFunction[Gray],[/Gray]@RgtPos[Gray],[/Gray]8000[Gray])[/Gray]
   [Blue]WHILE[/Blue] [Fuchsia]Left[/Fuchsia][Gray]([/Gray]@BeforeAgg[Gray],[/Gray]1[Gray])[/Gray][Gray]=[/Gray][red]'('[/red] [Gray]AND[/Gray] [Fuchsia]Right[/Fuchsia][Gray]([/Gray]@AggregateFunction[Gray],[/Gray]1[Gray])[/Gray][Gray]=[/Gray][red]')'[/red] [Blue]BEGIN[/Blue]
      [Blue]SET[/Blue] @BeforeAgg[Gray]=[/Gray][Fuchsia]SubString[/Fuchsia][Gray]([/Gray]@BeforeAgg[Gray],[/Gray]2[Gray],[/Gray]8000[Gray])[/Gray]
      [Blue]SET[/Blue] @AggregateFunction[Gray]=[/Gray][Fuchsia]Left[/Fuchsia][Gray]([/Gray]@AggregateFunction[Gray],[/Gray][Fuchsia]Len[/Fuchsia][Gray]([/Gray]@AggregateFunction[Gray])[/Gray][Gray]-[/Gray]1[Gray])[/Gray]
   [Blue]END[/Blue]
   [Blue]SET[/Blue] @ChrPos[Gray]=[/Gray]1
   [Blue]SET[/Blue] @LftPos[Gray]=[/Gray]0[green]
   --Count the number of parentheses before the agg function
[/green]   [Blue]WHILE[/Blue] @ChrPos[Gray]<[/Gray][Gray]=[/Gray][Fuchsia]Len[/Fuchsia][Gray]([/Gray]@BeforeAgg[Gray])[/Gray] [Blue]BEGIN[/Blue]
      [Blue]IF[/Blue] [Fuchsia]SubString[/Fuchsia][Gray]([/Gray]@BeforeAgg[Gray],[/Gray]@ChrPos[Gray],[/Gray]1[Gray])[/Gray][Gray]=[/Gray][red]'('[/red] [Blue]SET[/Blue] @LftPos[Gray]=[/Gray]@LftPos[Gray]+[/Gray]1
      [Blue]IF[/Blue] [Fuchsia]SubString[/Fuchsia][Gray]([/Gray]@BeforeAgg[Gray],[/Gray]@ChrPos[Gray],[/Gray]1[Gray])[/Gray][Gray]=[/Gray][red]')'[/red] [Blue]SET[/Blue] @LftPos[Gray]=[/Gray]@LftPos[Gray]-[/Gray]1
      [Blue]SET[/Blue] @ChrPos[Gray]=[/Gray]@ChrPos[Gray]+[/Gray]1
   [Blue]END[/Blue]
   [Blue]SET[/Blue] @ChrPos[Gray]=[/Gray][Fuchsia]Len[/Fuchsia][Gray]([/Gray]@AggregateFunction[Gray])[/Gray][green]
   --Count back from the right the same number of parenthesis from above
[/green]   [Blue]WHILE[/Blue] @LftPos[Gray]>[/Gray][Gray]-[/Gray]1 [Blue]BEGIN[/Blue]  
      [Blue]IF[/Blue] [Fuchsia]SubString[/Fuchsia][Gray]([/Gray]@AggregateFunction[Gray],[/Gray]@ChrPos[Gray],[/Gray]1[Gray])[/Gray][Gray]=[/Gray][red]'('[/red] [Blue]SET[/Blue] @LftPos[Gray]=[/Gray]@LftPos[Gray]+[/Gray]1
      [Blue]IF[/Blue] [Fuchsia]SubString[/Fuchsia][Gray]([/Gray]@AggregateFunction[Gray],[/Gray]@ChrPos[Gray],[/Gray]1[Gray])[/Gray][Gray]=[/Gray][red]')'[/red] [Blue]SET[/Blue] @LftPos[Gray]=[/Gray]@LftPos[Gray]-[/Gray]1
      [Blue]SET[/Blue] @ChrPos[Gray]=[/Gray]@ChrPos[Gray]-[/Gray]1
      [Blue]IF[/Blue] @ChrPos[Gray]=[/Gray]0 [Blue]BREAK[/Blue]
   [Blue]END[/Blue]
   [Blue]IF[/Blue] @ChrPos[Gray]=[/Gray]0 [Blue]BEGIN[/Blue]
      [Blue]PRINT[/Blue] [red]'Your parenthesis are not balanced in Parameter #2.'[/red]
      [Blue]RETURN[/Blue]
   [Blue]END[/Blue][green]
   --Get the tail end of non-agg functions
[/green]   [Blue]SET[/Blue] @AfterAgg[Gray]=[/Gray][Fuchsia]SubString[/Fuchsia][Gray]([/Gray]@AggregateFunction[Gray],[/Gray]@ChrPos[Gray]+[/Gray]2[Gray],[/Gray]8000[Gray])[/Gray]  [green]
   --This is the agg function with any INTERIOR functions
[/green]   [Blue]SET[/Blue] @AggregateFunction[Gray]=[/Gray][Fuchsia]Left[/Fuchsia][Gray]([/Gray]@AggregateFunction[Gray],[/Gray]@ChrPos[Gray]+[/Gray]1[Gray])[/Gray]  [green]
----- Finished with @AggregateFunction -----
[/green]
[green]
   --Hunt for SQL injections that use WMD.
[/green]   [Blue]CREATE[/Blue] [Blue]TABLE[/Blue] #WMD [Gray]([/Gray]Weapon [Blue]varchar[/Blue][Gray]([/Gray]128[Gray])[/Gray][Gray])[/Gray]
   [Blue]INSERT[/Blue] [Blue]INTO[/Blue] #WMD
      [Blue]SELECT[/Blue] [red]' alter '[/red] [Blue]UNION[/Blue]
      [Blue]SELECT[/Blue] [red]' bcp '[/red] [Blue]UNION[/Blue]
      [Blue]SELECT[/Blue] [red]' delete '[/red] [Blue]UNION[/Blue]
      [Blue]SELECT[/Blue] [red]' drop '[/red] [Blue]UNION[/Blue]
      [Blue]SELECT[/Blue] [red]' exec '[/red] [Blue]UNION[/Blue]
      [Blue]SELECT[/Blue] [red]' exec('[/red] [Blue]UNION[/Blue]
      [Blue]SELECT[/Blue] [red]' execute '[/red] [Blue]UNION[/Blue]
      [Blue]SELECT[/Blue] [red]' insert '[/red] [Blue]UNION[/Blue]
      [Blue]SELECT[/Blue] [red]' openquery '[/red] [Blue]UNION[/Blue]
      [Blue]SELECT[/Blue] [red]' restore '[/red] [Blue]UNION[/Blue]
      [Blue]SELECT[/Blue] [red]' shutdown '[/red] [Blue]UNION[/Blue]
      [Blue]SELECT[/Blue] [red]' truncate '[/red] [Blue]UNION[/Blue]
      [Blue]SELECT[/Blue] [red]' update '[/red] [Blue]UNION[/Blue]
      [Blue]SELECT[/Blue] [red]' sp_sqlexec '[/red] [Blue]UNION[/Blue]  [green]
--Listed here and below so that you can remove
[/green][green]--the sysobjects insert from below (if you like)
[/green][green]--but still keep the most dangerous ones in #WMD.
[/green]      [Blue]SELECT[/Blue] [red]' sp_executesql '[/red] [Blue]UNION[/Blue]  
      [Blue]SELECT[/Blue] [red]' xp_cmdshell '[/red]  
   [Blue]INSERT[/Blue] [Blue]INTO[/Blue] #WMD
      [Blue]SELECT[/Blue] [red]';'[/red][Gray]+[/Gray][Fuchsia]LTrim[/Fuchsia][Gray]([/Gray]Weapon[Gray])[/Gray] [Blue]FROM[/Blue] #WMD [Blue]UNION[/Blue]
      [Blue]SELECT[/Blue] [Name] [Blue]FROM[/Blue] Master.dbo.sysobjects [Blue]WHERE[/Blue] xType[Gray]=[/Gray][red]'P'[/red] [Gray]OR[/Gray] xType[Gray]=[/Gray][red]'X'[/red][green]
   --Strip out literals from the parameters so they are not identified as keywords
[/green]   [Blue]DECLARE[/Blue] @AllParameters [Blue]varchar[/Blue][Gray]([/Gray]8000[Gray])[/Gray]
   [Blue]DECLARE[/Blue] @ParseParam [Blue]varchar[/Blue] [Gray]([/Gray]8000[Gray])[/Gray]
   [Blue]DECLARE[/Blue] @Cnt [Blue]int[/Blue][Gray],[/Gray] @End [Blue]int[/Blue]
   [Blue]SET[/Blue] @AllParameters[Gray]=[/Gray][red]' '[/red][Gray]+[/Gray]@QueryTemplate[Gray]+[/Gray][red]' '[/red][Gray]+[/Gray]@AggregateFunction[Gray]+[/Gray][red]' '[/red][Gray]+[/Gray]@PivotColumn[Gray]+[/Gray][red]' '[/red][Gray]+[/Gray][Fuchsia]Char[/Fuchsia][Gray]([/Gray]39[Gray])[/Gray]
   [Blue]SET[/Blue] @ParseParam[Gray]=[/Gray][red]''[/red]
   [Blue]SET[/Blue] @ChrPos[Gray]=[/Gray]1
   [Blue]SET[/Blue] @Cnt[Gray]=[/Gray]1
   [Blue]WHILE[/Blue] @ChrPos[Gray]<[/Gray][Fuchsia]Len[/Fuchsia][Gray]([/Gray]@AllParameters[Gray])[/Gray] [Blue]BEGIN[/Blue]
      [Blue]SET[/Blue] @Cnt[Gray]=[/Gray]@Cnt[Gray]+[/Gray]1
      [Blue]SET[/Blue] @End[Gray]=[/Gray][Fuchsia]CharIndex[/Fuchsia][Gray]([/Gray][Fuchsia]Char[/Fuchsia][Gray]([/Gray]39[Gray])[/Gray][Gray],[/Gray]@AllParameters[Gray],[/Gray]@ChrPos[Gray])[/Gray]
      [Blue]IF[/Blue] @Cnt [Gray]%[/Gray] 2 [Gray]=[/Gray] 0 [Blue]SET[/Blue] @ParseParam[Gray]=[/Gray]@ParseParam[Gray]+[/Gray][Fuchsia]SubString[/Fuchsia][Gray]([/Gray]@AllParameters[Gray],[/Gray]@ChrPos[Gray],[/Gray]@End[Gray]-[/Gray]@ChrPos[Gray])[/Gray]
      [Blue]SET[/Blue] @ChrPos[Gray]=[/Gray]@End[Gray]+[/Gray]1
   [Blue]END[/Blue][green]
   --Now check for WMD
[/green]   [Blue]SET[/Blue] @SqlPivot[Gray]=[/Gray][red]''[/red]                         
   [Blue]SELECT[/Blue] @SqlPivot[Gray]=[/Gray]@SqlPivot[Gray]+[/Gray]
         [Blue]CASE[/Blue] [Fuchsia]CharIndex[/Fuchsia][Gray]([/Gray]Weapon[Gray],[/Gray]@ParseParam[Gray])[/Gray]
            [Blue]WHEN[/Blue] 0 [Blue]THEN[/Blue] [red]''[/red] [Blue]ELSE[/Blue] [red]', '[/red][Gray]+[/Gray][Fuchsia]LTrim[/Fuchsia][Gray]([/Gray][Fuchsia]Rtrim[/Fuchsia][Gray]([/Gray]Weapon[Gray])[/Gray][Gray])[/Gray] [Blue]END[/Blue] 
      [Blue]FROM[/Blue] #WMD

   [Blue]DROP[/Blue] [Blue]TABLE[/Blue] #WMD
   [Blue]IF[/Blue] [Fuchsia]Len[/Fuchsia][Gray]([/Gray]@SqlPivot[Gray])[/Gray][Gray]>[/Gray]0 [Blue]BEGIN[/Blue]
      [Blue]PRINT[/Blue] [red]'Your parameters contain a possible SQL injection attack:'[/red][Gray]+[/Gray][Fuchsia]Char[/Fuchsia][Gray]([/Gray]13[Gray])[/Gray][Gray]+[/Gray][Fuchsia]SubString[/Fuchsia][Gray]([/Gray]@SqlPivot[Gray],[/Gray]3[Gray],[/Gray]8000[Gray])[/Gray][Gray]+[/Gray][red]'.'[/red]
      [Blue]RETURN[/Blue]
   [Blue]END[/Blue]
[green]
   -- Use @SqlPivot to modify @QueryTemplate by making the first column the pivot column, run the query and insert
[/green]   [green]-- the PivotColumn values into a global temporary table.  The values will become the names of the cross tab columns.
[/green]   [Blue]SET[/Blue] @SqlPivot [Gray]=[/Gray] @QueryTemplate
   [Blue]SET[/Blue] @ChrPos [Gray]=[/Gray] [Fuchsia]CharIndex[/Fuchsia][Gray]([/Gray][red]'select '[/red][Gray],[/Gray]@SqlPivot[Gray])[/Gray] 
   [Blue]SET[/Blue] @SqlPivot [Gray]=[/Gray] [Fuchsia]Stuff[/Fuchsia][Gray]([/Gray]@SqlPivot[Gray],[/Gray]@ChrPos[Gray]+[/Gray]7[Gray],[/Gray]0[Gray],[/Gray]@PivotColumn [Gray]+[/Gray] [red]' as PivotColumn, '[/red][Gray])[/Gray]
   [Blue]SET[/Blue] @ChrPos [Gray]=[/Gray] [Fuchsia]CharIndex[/Fuchsia][Gray]([/Gray][red]' from '[/red][Gray],[/Gray]@SqlPivot[Gray])[/Gray]
   [Blue]SET[/Blue] @SqlPivot [Gray]=[/Gray] [Fuchsia]Stuff[/Fuchsia][Gray]([/Gray]@SqlPivot[Gray],[/Gray]@ChrPos[Gray],[/Gray]0[Gray],[/Gray][red]' INTO ##CrossTab '[/red][Gray])[/Gray]
[green]
--Find the last GROUP BY clause.
[/green]   [Blue]SET[/Blue] @ChrPos [Gray]=[/Gray] [Fuchsia]CharIndex[/Fuchsia][Gray]([/Gray][red]' group by '[/red][Gray],[/Gray]@SqlPivot[Gray])[/Gray]
   [Blue]WHILE[/Blue] [Fuchsia]CharIndex[/Fuchsia][Gray]([/Gray][red]' group by '[/red][Gray],[/Gray]@SqlPivot[Gray],[/Gray]@ChrPos[Gray]+[/Gray]1[Gray])[/Gray][Gray]>[/Gray]0
      [Blue]SET[/Blue] @ChrPos [Gray]=[/Gray] [Fuchsia]CharIndex[/Fuchsia][Gray]([/Gray][red]' group by '[/red][Gray],[/Gray]@SqlPivot[Gray],[/Gray]@ChrPos[Gray]+[/Gray]1[Gray])[/Gray]
   [Blue]SET[/Blue] @SqlPivot [Gray]=[/Gray] [Fuchsia]Stuff[/Fuchsia][Gray]([/Gray]@SqlPivot[Gray],[/Gray]@ChrPos[Gray]+[/Gray]10[Gray],[/Gray]0[Gray],[/Gray]@PivotColumn[Gray]+[/Gray][red]', '[/red][Gray])[/Gray]
[green]
   -- Check to see if someone else is using ##CrossTab at the same time and wait 10 seconds.
[/green]   [Blue]IF[/Blue] Exists [Gray]([/Gray][Blue]SELECT[/Blue] [Gray]*[/Gray] [Blue]FROM[/Blue] TempDB.dbo.SysObjects 
         [Blue]WHERE[/Blue] [ID] [Gray]=[/Gray] [Fuchsia]Object_ID[/Fuchsia][Gray]([/Gray]N[red]'TempDB.[dbo].[##CrossTab]'[/red][Gray])[/Gray] [Gray]AND[/Gray] xType[Gray]=[/Gray][red]'U'[/red][Gray])[/Gray]
      [Blue]WAITFOR[/Blue] [Blue]DELAY[/Blue] [red]'000:00:10'[/red]
   [Blue]IF[/Blue] Exists [Gray]([/Gray][Blue]SELECT[/Blue] [Gray]*[/Gray] [Blue]FROM[/Blue] TempDB.dbo.SysObjects 
         [Blue]WHERE[/Blue] [ID] [Gray]=[/Gray] [Fuchsia]Object_ID[/Fuchsia][Gray]([/Gray]N[red]'TempDB.[dbo].[##CrossTab]'[/red][Gray])[/Gray] [Gray]AND[/Gray] xType[Gray]=[/Gray][red]'U'[/red][Gray])[/Gray] [Blue]BEGIN[/Blue]
      [Blue]PRINT[/Blue] [red]'There is another global temporary table named ##CrossTab.  Cannot continue.'[/red]
       [Blue]RETURN[/Blue]
   [Blue]END[/Blue]

   [Blue]EXEC[/Blue] [Gray]([/Gray]@SqlPivot[Gray])[/Gray]
   [Blue]IF[/Blue] [Gray]NOT[/Gray] Exists [Gray]([/Gray][Blue]SELECT[/Blue] [Gray]*[/Gray] [Blue]FROM[/Blue] TempDB.dbo.SysObjects 
         [Blue]WHERE[/Blue] [ID] [Gray]=[/Gray] [Fuchsia]Object_ID[/Fuchsia][Gray]([/Gray]N[red]'TempDB.[dbo].[##CrossTab]'[/red][Gray])[/Gray] [Gray]AND[/Gray] xType[Gray]=[/Gray][red]'U'[/red][Gray])[/Gray] [Blue]BEGIN[/Blue]
      [Blue]PRINT[/Blue] [red]'There is an error in Parameter #3.'[/red]
      [Blue]RETURN[/Blue]
   [Blue]END[/Blue]
[green]
   -- Create a template for each CASE clause that will needed for the crosstab columns in the SELECT clause.
[/green]   [green]-- The @AfterAgg gets added here, but the @BeforeAgg is inserted later.
[/green]   [Blue]SET[/Blue] @AggregateFunction[Gray]=[/Gray][Fuchsia]Stuff[/Fuchsia][Gray]([/Gray]@AggregateFunction[Gray],[/Gray] [Fuchsia]Len[/Fuchsia][Gray]([/Gray]@AggregateFunction[Gray])[/Gray][Gray],[/Gray] 1[Gray],[/Gray] [red]' END)'[/red] [Gray]+[/Gray] @AfterAgg[Gray])[/Gray]
[green]
   -- Determine whether or not to use a ' to surround the comparison values in the CASE clause.
[/green]   [green]-- Varchar, Char and Date data types require the apostrophe whereas numbers don't require a delimiter.
[/green]
   [Blue]SELECT[/Blue] @Delimiter [Gray]=[/Gray] [Fuchsia]Left[/Fuchsia][Gray]([/Gray][red]''''[/red][Gray],[/Gray] [Fuchsia]COUNT[/Fuchsia][Gray]([/Gray][Gray]*[/Gray][Gray])[/Gray][Gray])[/Gray]
       [Blue]FROM[/Blue] ##CrossTab  [Blue]WHERE[/Blue] [Fuchsia]IsNumeric[/Fuchsia][Gray]([/Gray][Fuchsia]Cast[/Fuchsia][Gray]([/Gray]PivotColumn [Blue]AS[/Blue] [Blue]varchar[/Blue][Gray]([/Gray]8000[Gray])[/Gray][Gray])[/Gray][Gray])[/Gray][Gray]=[/Gray]0
[green]
   -- Generate all of the CASE statements needed in the SELECT clause.
[/green]   [Blue]SELECT[/Blue] @SqlCase[Gray]=[/Gray][Fuchsia]Coalesce[/Fuchsia][Gray]([/Gray]@SqlCase[Gray],[/Gray][red]''[/red][Gray])[/Gray] [Gray]+[/Gray][red]','[/red] [Gray]+[/Gray] [Fuchsia]Char[/Fuchsia][Gray]([/Gray]13[Gray])[/Gray][Gray]+[/Gray][red]'      ['[/red] [Gray]+[/Gray] [Fuchsia]IsNull[/Fuchsia][Gray]([/Gray][Fuchsia]Convert[/Fuchsia][Gray]([/Gray][Blue]varchar[/Blue][Gray]([/Gray]8000[Gray])[/Gray][Gray],[/Gray] PivotColumn[Gray])[/Gray][Gray],[/Gray][red]'NULLS'[/red][Gray])[/Gray] [Gray]+[/Gray] [red]'] = '[/red] [Gray]+[/Gray] 
         @BeforeAgg[Gray]+[/Gray][Fuchsia]Stuff[/Fuchsia][Gray]([/Gray]@AggregateFunction[Gray],[/Gray][Fuchsia]CharIndex[/Fuchsia][Gray]([/Gray] [red]'('[/red][Gray],[/Gray] @AggregateFunction [Gray])[/Gray][Gray]+[/Gray]1[Gray],[/Gray] 0[Gray],[/Gray] [red]'CASE '[/red] [Gray]+[/Gray] @PivotColumn [Gray]+[/Gray] [red]' WHEN '[/red] 
         [Gray]+[/Gray] @Delimiter [Gray]+[/Gray] [Fuchsia]IsNull[/Fuchsia][Gray]([/Gray][Fuchsia]Replace[/Fuchsia][Gray]([/Gray][Fuchsia]Convert[/Fuchsia][Gray]([/Gray][Blue]varchar[/Blue][Gray]([/Gray]8000[Gray])[/Gray][Gray],[/Gray] PivotColumn[Gray])[/Gray][Gray],[/Gray][red]''''[/red][Gray],[/Gray][red]''''''[/red][Gray])[/Gray][Gray],[/Gray][red]'NULL'[/red][Gray])[/Gray] [Gray]+[/Gray] @Delimiter [Gray]+[/Gray] [red]' THEN '[/red] [Gray])[/Gray] 
      [Blue]FROM[/Blue] [Gray]([/Gray][Blue]SELECT[/Blue] [Blue]DISTINCT[/Blue] [Blue]TOP[/Blue] 100 [Blue]PERCENT[/Blue] PivotColumn [Blue]FROM[/Blue] ##CrossTab [Blue]ORDER[/Blue] [Blue]BY[/Blue] PivotColumn[Gray])[/Gray] DistinctPivots
[green]
   ---Fix syntax of NULLS to say IS NULL instead of WHEN NULL.
[/green]   [Blue]SET[/Blue] @ChrPos [Gray]=[/Gray] [Fuchsia]CharIndex[/Fuchsia][Gray]([/Gray][red]'[NULLS]'[/red][Gray],[/Gray]@SqlCase[Gray])[/Gray] 
   [Blue]IF[/Blue] @ChrPos[Gray]>[/Gray]0 [Blue]BEGIN[/Blue]
      [Blue]SET[/Blue] @ChrPos [Gray]=[/Gray] [Fuchsia]CharIndex[/Fuchsia][Gray]([/Gray][red]'(CASE '[/red][Gray],[/Gray]@SqlCase[Gray],[/Gray]@ChrPos[Gray])[/Gray]
      [Blue]SET[/Blue] @SqlCase [Gray]=[/Gray] [Fuchsia]Stuff[/Fuchsia] [Gray]([/Gray]@SqlCase[Gray],[/Gray]@ChrPos[Gray]+[/Gray]6[Gray],[/Gray]0[Gray],[/Gray][red]'WHEN '[/red][Gray])[/Gray]
      [Blue]SET[/Blue] @ChrPos [Gray]=[/Gray] [Fuchsia]CharIndex[/Fuchsia][Gray]([/Gray][red]' WHEN '[/red][Gray],[/Gray]@SqlCase[Gray],[/Gray]@ChrPos[Gray]+[/Gray]10[Gray])[/Gray]
      [Blue]SET[/Blue] @SqlCase [Gray]=[/Gray] [Fuchsia]Stuff[/Fuchsia] [Gray]([/Gray]@SqlCase[Gray],[/Gray] @ChrPos[Gray]+[/Gray]1[Gray],[/Gray]9[Gray]+[/Gray]2[Gray]*[/Gray][Fuchsia]Len[/Fuchsia][Gray]([/Gray]@Delimiter[Gray])[/Gray][Gray],[/Gray][red]'IS NULL'[/red][Gray])[/Gray]
   [Blue]END[/Blue]

   [Blue]DROP[/Blue] [Blue]TABLE[/Blue] ##CrossTab
[green]
   -- Insert the CASE statements into the SELECT clause that was provided by the user.
[/green]   [Blue]SELECT[/Blue] @QueryTemplate[Gray]=[/Gray][Fuchsia]Stuff[/Fuchsia][Gray]([/Gray]@QueryTemplate[Gray],[/Gray] [Fuchsia]CharIndex[/Fuchsia][Gray]([/Gray][red]' FROM '[/red][Gray],[/Gray] @QueryTemplate[Gray])[/Gray][Gray],[/Gray] 0[Gray],[/Gray] @SqlCase [Gray]+[/Gray] [Fuchsia]Char[/Fuchsia][Gray]([/Gray]13[Gray])[/Gray] [Gray]+[/Gray] [red]'  '[/red][Gray])[/Gray]
   [Blue]IF[/Blue] [Fuchsia]Len[/Fuchsia][Gray]([/Gray]@QueryTemplate[Gray])[/Gray][Gray]=[/Gray]8000
      [Blue]PRINT[/Blue] [red]'WARNING: The length of the query string is too long!'[/red]
   [Blue]PRINT[/Blue] @QueryTemplate
[Blue]SET[/Blue] [Blue]Ansi_Warnings[/Blue] [Blue]ON[/Blue]
[Blue]GO[/Blue]
Run the SP this way:
Code: 
[Blue]EXEC[/Blue] crosstabprint
[red]'select lastname from license group by lastname'[/red][Gray],[/Gray]
[red]'max(licenseno)'[/red][Gray],[/Gray]
[red]'State'[/red]
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]
 
Upvote 0 Downvote
Wow, thanks a bunch, I would have never come up with this on my own.

Jim
 
Upvote 0 Downvote
Is it possible to make the code that displays in the message box of query analyzer be dynamic? I need to run that code but the data in the tables changes?

How could I do that?
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

SashaBuilder3
  • Locked
  • Question Question
select a single row based on conditions in multiple rows
Replies
3
Views
1K
SashaBuilder3
SashaBuilder3
peac3
  • Locked
  • Question Question
Consolidate multiple rows into single row
Replies
4
Views
2K
Chris Miller
Chris Miller
Cpreston
  • Locked
  • Question Question
Group results into one row
2
Replies
37
Views
3K
Chris Miller
Chris Miller
MathiasB
  • Locked
  • Question Question
Parameterised sql server query
Replies
8
Views
944
Chriss Miller
C
peac3
  • Locked
  • Question Question
split one row into multiple rows... help pls
Replies
12
Views
2K
Chris Miller
Chris Miller

Part and Inventory Search

Sponsor

Back
Top