Need SQL Server Stored Procedure for This Query

Question

I have a ASPX.NET DataGrid and im trying to USE a select LIKE 'X'% from a table that has 1 field called location. im trying to display the locations that start with a certain letter (example wxxx,axxx,fxxx,) in different columns in my data grid.

I am trying to display more than 1 column in my datagrid using a SP shown below. The issue is that the table locationMaster has only 1 field called location. The field Location has mutable location numbers that start with different letters (example w1002, w1003, 00159, 00526). What I would like to do is use a sp to display the wxxxx locations in one column in my datagrid and 0xxxx in another. If i just simply run

SELECT DISTINCT 
    LM.LOCATION AS 'LOCATIONS', 
    LM.COUNTLEVEL AS 'COUNTLEVEL' 
FROM  
    SOH S WITH(NOLOCK)  
JOIN LOCATIONMASTER LM ON LM.LMID = S.LMID 
WHERE 
    LM.COUNTLEVEL = 1 
    AND LM.LOCATION NOT IN ('RECOU','PROBLEM','TOSTOCK','PYXVLOC')

My Datagrid has only 1 column with all the locations and the page will be very lengthy If i could somehow use LIKE 'W%' AND LIKE '0%' in a sp and create two columns

SELECT
  DISTINCT LM.LOCATION AS '0 LOCATIONS' ,
    LM.COUNTLEVEL AS 'COUNTLEVEL'
FROM  SOH S WITH(NOLOCK)
  JOIN LOCATIONMASTER LM ON LM.LMID = S.LMID
WHERE
   LM.COUNTLEVEL = 1 AND
   LM.LOCATION NOT IN ('RECOU','PROBLEM','TOSTOCK','PYXVLOC')
   AND LM.LOCATION LIKE '0%'

SELECT
   DISTINCT LM.LOCATION AS 'A LOCATIONS' ,
   LM.COUNTLEVEL AS 'COUNTLEVEL'
FROM  SOH S WITH(NOLOCK)
  JOIN LOCATIONMASTER LM ON LM.LMID = S.LMID
WHERE
   LM.COUNTLEVEL = 1 AND
   LM.LOCATION NOT IN ('RECOU','PROBLEM','TOSTOCK','PYXVLOC')
   AND LM.LOCATION LIKE 'A%'**

And here is my datagrid code

<Columns>
    <asp:BoundColumn DataField="COUNTLEVEL" Visible="false"/>
    <asp:TemplateColumn HeaderText="LOCATION">
       <ItemTemplate>
          <a href='confirmRecount.aspx?Var=<%# DataBinder.Eval(Container.DataItem ,"0 LOCATIONS")%>'>
          <%# DataBinder.Eval(Container.DataItem, "0 LOCATIONS")%>
          </a>
       </ItemTemplate>
    </asp:TemplateColumn>
</Columns>

Answer 1

What you want to do is not realy logical, data in a row relates to other data in that row it could not be done with a simple SQL select. Let's say you had a small table with 2 rows:

**LOCATION**         **COUNTLEVEL**
   W1234                  10
   A7654                  23

Now you want to transform it so that W1234 and A7654 were in 2 different columns. What would happen to the count level field. You would then have 1 row with theee columns and you would have 2 count levels that no longer related to their respective rows they would now relate to different columns, what would go in the count level column? So you would have to try and do something like this:

**LOCATION A**  **COUNTLEVELA**   **LOCATION W**   **COUNTLEVELW**
    NULL             NULL              W1234             10   
   A7654             23                 NULL            NULL 

But i don't think that is going to be in any way useful.

The only way you can project vertical rows into horizontal columns is to PIVOT however what you end up with there is the values from one field become column headers and the values from another become your rows so you would have something like below:

                  **W1234**            **A7654**
LOCATION COUNT      10                    23

However in your case this would just make your grid view have one row and many columns, really for this to be usefull you need a third variable to group by vertically such as deptartment.

If you simply want to have the amount of vertical space your data grid takes up on the page the you can just use 2 data grids and have the SP return 2 data sets, but then the data on matching rows in the 2 data grids will not relate to each other in any way.