How to get specific columns from a table into a query

Transact-SQL
1

I have a table which has store numbers as column names. There will be qty in the data fields. Now, If I need to use this qty from all locations, I need to write a query by passing only store numbers as a parameter and then take the values from it. There could be a new store number added to the table. So, Please look at the table declaration and help me write a query on how to retrieve overall Qty per StoreNumber in a query. My table declaration is:

[CREATE TABLE [dbo].[PJ_Matrix_RowDstbn_T](
[Distbn_Date] [datetime] NOT NULL,
[ItemNumber] varchar NOT NULL,
[Variation] varchar NOT NULL,
[ItemPrice] varchar NULL,
[206] varchar NOT NULL,
[221] varchar NOT NULL,
[226] varchar NOT NULL,
[230] varchar NOT NULL,
[231] varchar NOT NULL,
[237] varchar NOT NULL,
[240] varchar NOT NULL,
[241] varchar NOT NULL,
[243] varchar NOT NULL,
[244] varchar NOT NULL,
[301] varchar NOT NULL,
[302] varchar NOT NULL,
[303] varchar NOT NULL,
[304] varchar NOT NULL,
[305] varchar NOT NULL,
[306] varchar NOT NULL,
[307] varchar NOT NULL,
[308] varchar NOT NULL,
[310] varchar NOT NULL,
[312] varchar NOT NULL,
[313] varchar NOT NULL,
[314] varchar NOT NULL,
[315] varchar NOT NULL,
[316] varchar NOT NULL,
[318] varchar NOT NULL,
[319] varchar NOT NULL,
[320] varchar NOT NULL,
[321] varchar NOT NULL,
[322] varchar NOT NULL,
[323] varchar NOT NULL,
[324] varchar NOT NULL,
[326] varchar NOT NULL,
[333] varchar NOT NULL,
[780] varchar NOT NULL,
[1062] varchar NOT NULL,
[1070] varchar NOT NULL,
[1093] varchar NOT NULL,
[1166] varchar NULL,
[785] varchar NULL,
[1174] varchar NULL,
[1176] varchar NULL,
[1177] varchar NULL,
[1195] varchar NULL,
[2000] varchar NULL,
[2001] varchar NULL,
[2002] varchar NULL,
[@Store_Number] varchar NULL,
[2003] varchar NULL,
[2004] varchar NULL,
[2005] varchar NULL,
[2006] varchar NULL
) ON [PRIMARY]

GO](http://)

2

As mentioned in your other post it would be much better if the Store Data was in a ChildTable of the main table.

One option would be to create a VIEW which "faked" that arrangement (if you cannot change the actual [PJ_Matrix_RowDstbn_T] table

Using UNPIVOT would be one way to transform the one-column-per-store into one-store-per-row

1 Like
3

Since there are many different ways the results could be presented, please tell us what you'd like to see for column names in the return if such a query were to have store numbers 206, 221, and 226 passed to it.